topic Re: Complex dynamic computation of value based on selection in App Development

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

danette — Wed, 02 Nov 2022 19:54:45 GMT

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

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

rbartley — Wed, 02 Nov 2022 19:55:24 GMT

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)

Re: Complex dynamic computation of value based on selection

danette — Fri, 14 Jan 2022 09:04:57 GMT

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.

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

Scotlyvictor — Wed, 02 Nov 2022 19:55:07 GMT

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)

Re: Complex dynamic computation of value based on selection

danette — Fri, 14 Jan 2022 10:06:55 GMT

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:

Re: Complex dynamic computation of value based on selection

Scotlyvictor — Fri, 14 Jan 2022 10:44:24 GMT

Hi,

What is the formula used in Cost ?

Can you please use sum(Cost) and check.

Re: Complex dynamic computation of value based on selection

danette — Fri, 14 Jan 2022 14:14:53 GMT

This way it works! Thanks

Re: Complex dynamic computation of value based on selection

rbartley — Fri, 14 Jan 2022 15:45:05 GMT

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:

Re: Complex dynamic computation of value based on selection

danette — Fri, 14 Jan 2022 16:03:32 GMT

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.