Max Date in Pivot Table - max by Dimension

[Scott2]

Hi All

I have a pivot table of data and want to write a measure that is a simple sum of the Quantity column where ExpiryDate = maximum ExpiryDate for the row and column dimensions applicable to that cell.

We've been battling this one for a while and are unable to even add a measure that actually returns the max ExpiryDate by dimension. It always returns the Max Date across all dimensions. Please can you advise how to get a max date by dimension and then how to use this in an set analysis expression to calculate the Sum of Quantity? I dont want to be hardcoding the dimensions in the expression either - expecting Qlik to do that bit based on the Pivot Table Dimensions. 

See below things we have tried. Any help greatly appreciated  🙂

Pivot Table measures and results as follows. I've also attached a screenshot of the results in the pivot. 

1. Sum(Quantity) - Returns the sum of Quantity by Dimension across all rows as expected.

2. Sum( {$<[ExpiryDate] = {"=$(Max([ExpiryDate]))"}>} Quantity ) - Returns Zero.  Expected it only to sum for the rows with the Max ExpiryDate within that Dimension. The Max([ExpiryDate]) doesn't find any date it seems and presumably why returns zero. 

3. Sum( {$<[ExpiryDate] = {"=$(=Max([ExpiryDate]))"}>} Quantity ) = Returns the same as sum of Sum(Quantity). I was expecting this to calculate the Max Expiry date across all the data (regardless of dimension) and then filter by that. It has calculated the max date but not applied it as a filter. 

4. DATE($(=Max([ExpiryDate]))) - This returns the max date over all the records. Not max date by dimension. 

FruitData:
NoConcatenate
Load * Inline [

FruitType,Colour,Quantity,DeliveryDate,ExpiryDate
Apple,Green,34,2023-08-30,2023-09-02
Apple,Green,55,2023-08-30,2023-09-10
Apple,Red,68,2023-08-30,2023-09-25
Apple,Red,100,2023-08-02,2023-08-16
Avocado,Green,166,2023-10-15,2023-11-06
Avocado,Green,196,2023-10-15,2023-01-03
Avocado,Brown,11116,2023-10-15,2023-04-01
];

[Ahidhar]

try this

Sum({<ExpiryDate={"=aggr(max(ExpiryDate),Colour,FruitType)"}>}Quantity)

shared below the results obtained

[Scott2]

thanks Adihar for your help on this. I had a look at the screenshot and values look correct except for Apple - Green the value should be 55 rather than 34. But your expression looks good. It's odd. - Am I missing something? 

[Ahidhar · Accepted solution]

try this Sum({<Flag={'1'}>}Quantity)

Don't know why it is not reading  the max value for Apple-Green

I created one Flag in load script and fixed it 

See if this helps

FruitData:
load FruitType,Colour,Quantity,date(date#(DeliveryDate,'YYYY-MM-DD'),'DD/MM/YYYY') as DeliveryDate,date(date#(ExpiryDate,'YYYY-MM-DD'),'DD/MM/YYYY') as ExpiryDate;
Load * Inline [

FruitType,Colour,Quantity,DeliveryDate,ExpiryDate
Apple,Green,34,2023-08-30,2023-09-02
Apple,Green,55,2023-08-30,2023-09-10
Apple,Red,68,2023-08-30,2023-09-25
Apple,Red,100,2023-08-02,2023-08-16
Avocado,Green,166,2023-10-15,2023-11-06
Avocado,Green,196,2023-10-15,2023-01-03
Avocado,Brown,11116,2023-10-15,2023-04-01
];
left join(FruitData)
tab1:
load FruitType,Colour,Date(max(ExpiryDate),'DD/MM/YYYY') as maxdate
resident FruitData
group by Colour,FruitType;

FruitData1:
load *,
if(ExpiryDate=maxdate,1,0) as Flag
resident FruitData;
drop table FruitData;

[marcus_sommer]

It's not solvable with a set analysis because that applies a column-level evaluation and not a row-level evaluation and also the $-sign expansion within the set analysis is evaluated only ones before the chart is calculated and this result is applied by each call.

This means you need to query the condition within an if-loop, maybe in this way:

if([ExpiryDate]=Max([ExpiryDate]), Sum(Quantity))

[Scott2]

Hi Marcus, thanks for the guidance on why it is not solvable in the way I had originally been trying. I had suspected that was the case based on some googling but was finding it difficult to find this written down explicitly in the Qlik help. 

I like your suggestion although it is giving me very unexpected results. I will try to make it work further. 

In the meantime I have just established the following solution that seems like it is working well. Further testing on my actual data still to be done though including checking performance. However so far It seems a good solution as I don't need to specify the pivot table dimensions. 

FirstSortedValue(Aggr(Sum([Quantity]), ExpiryDate), -ExpiryDate)

I can also specify set analysis to pre filter the data as needed which is also the next step in my use case : e.g.

FirstSortedValue({<DeliveryDate={"$(vUserSelectedDeliveryDate)"}>} Aggr(Sum([Quantity]), ExpiryDate), -ExpiryDate)

And further more I now have an additional use case to select the penultimate maximum ExpiryDate and that is also supported by the FirstSortedValue function by putting ,2 at end e.g. 

FirstSortedValue({<DeliveryDate={"$(vUserSelectedDeliveryDate)"}>} Aggr(Sum([Quantity]), ExpiryDate), -ExpiryDate,2)

Will let you and Ahidhar know if it passes my full testing. If you are aware of any issues with this approach, I'd appreciate any further guidance on that. Thanks again to both of you for taking the time to reply to my question! 

Scott

[marcus_sommer]

In general is this kind of approach ok. But an aggr() is a quite performance-intensive measurement and depending on the size of the data-set + the kind of data-model and the view-requirements in regard to the available system-resources and the usage it might become rather slowly.

If your expression-results are depending on the selection-state there is not really a way around an aggr() but if it's not depending on the selections the aggr() could be avoided by using appropriate flags, like suggested from @Ahidhar whereby by using interrecord-functions like peek() and previous() instead of an aggregation-load you could also flag the n-th occurrence.

[Scott2]

thanks both for your suggestion to use appropriate flags in the data load script. I would like to explore that further.

Is there a way for example to use 'previous' or other Qlik function to get the row that is the previous row where the DeliveryDate month is the previous calendar month (compared to the Delivery Date of the Current Row) and has the max ExpiryDate for that DeliveryDateMonth? 

[marcus_sommer]

You could rank the records with something like:

load *;
load *, if(FruitType = previous(FruitType), peek('Flag') + 1, 1) as Flag
resident Source order by FruitType, ExpiryDate desc;

Important is right sorting within the resident load and to query all needed conditions and exceptions within if-loops. If you need to consider beside the FruitType also the Colour and/or the periods of the various date-fields and so on you will need some more (nested) if-loops. Helpful may also be to create several flag-fields and/or some temporary fields and using a cascading-logic within preceding-loads to keep the single conditions as simple as possible.

[Scott2]

thanks both for going down this rabbit hole with me. 

Just to let you know (and anyone else that sees this thread in future) that i did get FirstSortedValue working by adding all the dimensions from my pivot in the aggr but then hit a blocker, which was that it doesnt work when you collapse a row dimension down, i mean it doesn't sum the underlying rows as one would expect from a pivot. (due to how FirstSortedValue with aggr works. Tried to work around it with Dimensionality() but that wasnt working as expected either again due to the aggr dimensions so i have given up on it. 

Have accepted  Ahidhar suggestion to do it in the script - i've also simplified my requirements a bit to make that easier for myself so dont need to peek/previous now.  

thanks again