Pivot Table: EXPAND and COLLAPSE give different value to me.

[jim_chan]

Hi there,

i am using a pivot table ,

When i EXPAND from Year to Month, i can see this Retail Lot , lot count  = 1, actually is to indicate this lot is open in March 2022, 25/3/2022.

Problem comes when i COLLAPSE back to Year.  It shows me 0 instead. Expected result , it should know 1, not 0. 

in the Lot Count expression , i have such script, 

IF(Month(Date_Opened) = Month,count(distinct Lot),0)

Anyone can help?

Thank you

Rgds

Jim

[MartW]

hey @jim_chan 

is this calculation done in the pivot table itself?

if so this could be that because you collapsed the Month field it doesn't get calculated anymore.

I would advice to create a field in the Load script editor or data-manager where this is precaculated. 

everytime you reload the app it is up to date and  even when you move up a level in the pivot the 1 is still displayed

If you liked the answer please give it a like and if it helped please accept this answer as the sollution

[jim_chan]

at 1st, the mesaurement is count(distinct Lot) . but is given the wron value to i changed it to IF(Month(Date_Opened) = Month,count(distinct Lot),0)

its a front end calculation

[MartW · Accepted solution]

Month field it doesn't get calculated anymore. because it is collapsed within the year.

is it possible to move this piece of script back to the backend of Qlik Sense (Data load editor or Datamanager)

when moved back, you can use this new field in the front-end