Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

User defined Pivot Table

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?

 SpendRevenue
Period 1 - 1/1/2020 - 1/8/2020100500
Period 2 - 1/9/2020 - 1/16/2020150700
Difference %50%40%

 

Labels (2)
1 Solution

Accepted Solutions
Highlighted
Master
Master

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

HirisH_V7_0-1589222395885.png

 

 

=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..

HirisH
“Aspire to Inspire before we Expire!”

View solution in original post

4 Replies
Highlighted
Master
Master

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

HirisH_V7_0-1589222395885.png

 

 

=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..

HirisH
“Aspire to Inspire before we Expire!”

View solution in original post

Highlighted
Creator
Creator

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

 

Highlighted
Master
Master

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

HirisH
“Aspire to Inspire before we Expire!”
Highlighted
Creator
Creator

Thanks!!