Double Pivot Table

[Ludovico Quercia]

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]

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: 

https://msdn.microsoft.com/en-us/library/hh231256.aspx  

https://oracle-base.com/articles/misc/lag-lead-analytic-functions  

 

Note: Your database should have unique date values. 

 

Please let us know if you have any further concerns. 

 

Regards, 

Vinoth K