User defined Pivot Table

[sanjujeeboy]

hi all,

i want to acheive the following layout in qliksense.

there are two date filters : Period 1 and Period 2 . The user selects the respective periods and the data should be populated as below.  how to achieve this?

	Spend	Revenue
Period 1 - 1/1/2020 - 1/8/2020	100	500
Period 2 - 1/9/2020 - 1/16/2020	150	700
Difference %	50%	40%

[HirisH_V7 · Accepted solution]

By using alternate states in each calendar objects and using the same in pivot table by  using synthetic dimension created with the Value List ..

=ValueList('Period I','Period II','Diff')  //Dimension

=If(ValueList('Period I','Period II','Diff')='Period I',
Min({PeriodI} ShipDate) & ' To ' & Max({PeriodI} ShipDate),
If(ValueList('Period I','Period II','Diff')='Period II',
Min({PeriodII} ShipDate) & ' To ' & Max({PeriodII} ShipDate),
'%'))  //Expression I 



=If(ValueList('Period I','Period II','Diff')='Period I',
Sum({<ShipDate={">=$(=Min({PeriodI}ShipDate))<=$(=Max({PeriodI} ShipDate))"}>}Sales),
If(ValueList('Period I','Period II','Diff')='Period II',
Sum({<ShipDate={">=$(=Min({PeriodII} ShipDate))<=$(=Max({PeriodII} ShipDate))"}>}Sales),
If(ValueList('Period I','Period II','Diff')='Diff',
Sum({<ShipDate={">=$(=Min({PeriodI} ShipDate))<=$(=Max({PeriodI} ShipDate))"}>}Sales)
-
Sum({<ShipDate={">=$=(Min({PeriodII} ShipDate))<=$(=Max({PeriodII} ShipDate))"}>}Sales)
)))   //Expression I I

PFA QVF For ref..

[sanjujeeboy]

Hi Hirish,

Thanks for quick response.

One doubt here, Like Sales i have 4 more KPIs Spend Revenue,Margin etc how can i write expression for these as well.

thanks

[HirisH_V7]

You can re write those expression with replacing sales fields. I just gave an example data for ref.

[sanjujeeboy]

Thanks!!