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. Image for the cookie policy date

Double Pivot Table

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

1 Reply

VK Vinoth Krishnamoorthy Syncfusion Team 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: 
                [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] 
                [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. 
Vinoth K 

Live Chat Icon For mobile
Up arrow icon