How to use Complex dynamic computation of value based on selection in Qlik Sense?

[danette]

Hello,

I have a table like this

Now, consider that the user can select a period of time. I want to add a column where there must be the most recent available cost per product. So, for example, if the user select Feb21 and March21, the table should be like the following.

I tried to compute lastCost using a set analysis where I tried to select the cost corresponding to max(time) per each product, but this is not ok because this automatically filter the table, leaving only the rows corresponding to max time, per product.

I need lastCost to build also some charts, using this cost to compute costs based on the most recent cost. I cannot do this in the etl because the most recent cost changes depending on the period selected by the user.

How could I accomplish such task?

Thanks

[rbartley]

Hi,

My preference would be to add the "last cost flag" field in your load script and then just use that in the set analysis, e.g.

=Sum({<"last cost flag"={1}>}cost)

[danette]

Hi,

last cost varies depending on the period that is selected by the user, so it cannot be pre-determined inside the load script. I don't need the absolute last cost, but the last cost based on the selection.

[Scotlyvictor · Accepted solution]

This would help you to achieve the desired result.

LOAD *, DATE#(Time,'DD-MM-YYYY') AS DATEFORMAT;
Load * inline [
Product ,Time , Cost
A,01-01-2021,1
A,01-02-2021,2
A,01-03-2021,4
B,01-01-2021,5
B,01-03-2021,3
C,01-03-2021,6
C,01-02-2021,7
C,01-01-2021,8

];

Expression:

Sum(Total <Product> {<DATEFORMAT={"$(=DATE(MAX(DATEFORMAT),'DD-MM-YYYY'))"}>}Cost)

[danette]

Hi,

this is what I already tried, but this automatically filter the rows, leaving only the rows corresponding to the max date. See following image.

I want to keep also the others rows and in every row there should be the most recent costs (that are the ones selected in the set analysis above, but also the other rows must continue to appear). Like this:

[Scotlyvictor · Accepted solution]

Hi,

What is the formula used  in Cost ?

Can you please use sum(Cost) and check.

[danette]

This way it works! Thanks

[rbartley]

It works if all products have the same last date, but not if they don't, e.g. if product A has a last date of 01-02-2021, but B and C have a last date of 01-03-2021, you don't get a latest value for B:

[danette]

Yes, I believe this is because in the set analysis the max date is 'general', I mean that the last date that is selected is the absolute max date, not by product.

To get the last date grouping by product I believe we should use AGGR. 

If you have any suggestions, that would be greatly appreciated.