We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy.
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Double Pivot Table

Thread ID:

Created:

Updated:

Platform:

Replies:

126061 Sep 21,2016 08:41 AM UTC Sep 22,2016 03:19 PM UTC Dashboard Platform 1
loading
Tags: Dashboard Widgets
Ludovico Quercia
Asked On September 21, 2016 08:41 AM UTC

Hi,
I would like to see PC sales in a year divided by month through Pivot Table.
I have a date picker that restrict data to a range.
I would that another Pivot Table show the same data in the same period of the previous year.
So i would that the first pivot table use as filter the data range but the secondo pivot table use a relative range (in this case range - 1 year).
How i can make this?
Best regards

Vinoth Krishnamoorthy [Syncfusion]
Replied On September 22, 2016 03:19 PM UTC

Hi Ludovico, 
 
Thank you for using Syncfusion Products. 
 
The mentioned requirement to display current year date range in one widget and corresponding date range of previous year in another widget, cannot be achieved through the Dashboard settings. However, it is possible to have the data prepared based on that through a custom query and connect through Dashboard Designer to create a dashboard as per the requirement. LAG function supported in SQL and ORACLE databases helps to create such queries.

Please follow the below steps to accomplish the data preparation as per the requirement
 
 
Step 1: 
Consider this is your table, having two columns having Date and Production. 
 
 
 
Step 2: 
Now you need create two custom columns using custom query, one having previous year and another one having previous year production information. 
 
Sample Query: 
SELECT  
                [dbo_Production].[Date] AS [dbo_Production_Date] 
                ,[dbo_Production].[Production] AS [dbo_Production_Production] 
           , DATEADD("YY",-1,[dbo_Production].[Date]) AS [dbo_Previous_Production_Date] 
           , LAG([dbo_Production].[Production], 365,0) OVER (ORDER BY [dbo_Production].[Date] ) as [dbo_Previous_Production] 
FROM  
                [dbo].[Production] AS [dbo_Production] 
 
 
 
Step 3: 
Configure Date and production column in PivotGrid 1: 
 
 
 
Configure Previous Date and Production column in PivotGrid2: 
 
 
 
Configure Date column in DatePicker control: 
 
 
 
Step 4: 
Now preview and filters the date, you can achieve your requirement  
 
 
 
 
 
Please check the below links for LEAD and LAG function usage: 
 
Note: Your database should have unique date values. 
 
Please let us know if you have any further concerns. 
 
Regards, 
Vinoth K 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

;