Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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% |
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..
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..
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
You can re write those expression with replacing sales fields. I just gave an example data for ref.
Thanks!!