How to display every Quarter of First month Sales data

[Lakshman_1031]

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

[mnvrma]

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]

Hi ,

Thanks for your  quick response.

see the below attached data for your reference.

Sep	65
Oct	14
Nov	74
Dec	67
Jan	35
Feb	65
Mar	15
Apr	48
May	98
Jun	68
Jul	24
Aug	66

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 1	65
Q 2	67
Q 3	15
Q 4	68

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]

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

Dashboard attached as well

[Lakshman_1031]

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]

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]

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 · Accepted solution]

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]

Hi ,

Thank you very much for your support.

finally i got solution.

Regards,

Lakshman

[lorenzoconforti]

No problem

Please mark the thread as solved

Lorenzo