Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Lakshman_1031
Partner - Contributor
Partner - Contributor

How to display every Quarter of First month Sales data

Hi All,

I Have Year, Quarter and Month with Sales data.

I have taken pivot table for displaying the data.

If i click ‘+’ symbol on year in pivot table, it will expand and showing Quarters and if I click ‘+’ on Quarters then expand and showing Months.

Q 1 having Sep, Oct, Nov and Q 2 having Dec, Jan, Feb like that

If I collapse Q 1 then I want to display the Sep data for Q 1 (Like Beginning Month of the Quarter Q 1 like Sep) and if collapse Q 2 then Display Dec for Q 2 Data like that.

How to Achieve that.

Please see the below attached example for reference.

Please suggest me How to Achieve This Scenario.

 

Thanks & Regards, 

lakshman

 

 

 

 

1 Solution

Accepted Solutions
lorenzoconforti
Specialist II
Specialist II

when the dimensions are over columns, then you need to use SecondaryDimensionality()

See attached

I'm sending my file as in yours Month is not a date, just character; sorting to get the latest month in the quarter doesn't work in your data structure. You need to fix that in the loading script otherwise you'll struggle to get it to work

=if(secondaryDimensionality() = 1, FirstSortedValue(aggr(sum(Value), Month), -Month),
if(secondaryDimensionality()=2, sum(Value), 'Error'))

View solution in original post

11 Replies
mnvrma
Partner - Contributor III
Partner - Contributor III

Do you want something like this...

Q1-Sep, Oct, Nov

Q2-Dec, Jan, Feb

I have attached the Capture image, please check it.

Lakshman_1031
Partner - Contributor
Partner - Contributor
Author

Hi ,

Thanks for your  quick response.

see the below attached data for your reference.

Sep65
Oct14
Nov74
Dec67
Jan35
Feb65
Mar15
Apr48
May98
Jun68
Jul24
Aug66

Q 1 having -Sep,Oct,Nov

Q 2 having -Dec, Jan, Feb

Q 3 having - Mar, Apr, May

Q 4 having -Jun , Jul, Aug

I have Year, Quarter and Month with sales data.

I have used pivot table in Qlikview if i expand the Year then display the Q 1, Q 2, Q 3, Q 4 and if i Expand the Quarters then display the Months like above table.
if i collapse Q 1 normally it will display sum of the Q 1 like Sep+Oct + Nov that is  Q 1 having 153 

But my requirement is if i collapse the Q 1 then show Q 1 is  65 like it is Sep data. Sep is the beginning month of the Quarter Q 1, in the same way all quarters having same thing. 

See the below table for your reference 

Q 165
Q 267
Q 315
Q 468

Again if i expand Q 1 then will show the Sep, Oct , Nov like that if i expand the all Quarters the will show all months.

if i collapse the year then will show 2020 is 65.

Please suggest me this scenario is possible in pivot table in Qlikview.

if it is possible please suggest me how to achieve this scenario. 

 

Regards,

lakshman

lorenzoconforti
Specialist II
Specialist II

You can achieve this using the dimensionality function. Basically, in your expression you add a condition based on what level the row in the pivot table is expanded:

=if(Dimensionality() = 1, FirstSortedValue(aggr(sum(Value), Month), -Month),
if(Dimensionality()=2, sum(Value), 'Error'))

 

In this screenshot the two tables are identical, the only difference is that the one in the left is expanded. The one in the right, when not expanded (i.e. when dimensionality is 1) will show the sum of the last month in the quarter

Pivot.png

Dashboard attached as well

Lakshman_1031
Partner - Contributor
Partner - Contributor
Author

Hi ,

Thanks a lot for your replay.

I have achieved using dimensionality function.

If i move Quarter and Month in Expression side, it is not working. 

is it possible to work in Expression side hierarchy

like Sales ->Year ->Quarter -Month 

 

Regards,

lakshman

 

 

 

lorenzoconforti
Specialist II
Specialist II

Not sure if I fully understand. Are you trying to add the year column as well? if that's the case, add the extra column and bump up the dimensionality by 1

=if(Dimensionality() = 2, FirstSortedValue(aggr(sum(Value), Month), -Month),
if(Dimensionality()=3, sum(Value), 'Error'))

If this is not what you are trying to achieve, can you please send another picture of the desired outcome?

Lakshman_1031
Partner - Contributor
Partner - Contributor
Author

Hi ,

I have attached sample Qvw.

previously what your sent qvw as it is but 

same way in expression side. 

please suggest me any solution.

 

Regards,

lakshman

lorenzoconforti
Specialist II
Specialist II

when the dimensions are over columns, then you need to use SecondaryDimensionality()

See attached

I'm sending my file as in yours Month is not a date, just character; sorting to get the latest month in the quarter doesn't work in your data structure. You need to fix that in the loading script otherwise you'll struggle to get it to work

=if(secondaryDimensionality() = 1, FirstSortedValue(aggr(sum(Value), Month), -Month),
if(secondaryDimensionality()=2, sum(Value), 'Error'))

Lakshman_1031
Partner - Contributor
Partner - Contributor
Author

Hi ,

Thank you very much for your support.

finally i got solution.

 

Regards,

Lakshman

lorenzoconforti
Specialist II
Specialist II

No problem

Please mark the thread as solved

Lorenzo