topic Dynamic Modifier in Set Analysis in QlikView

Dynamic Modifier in Set Analysis

luksse7 — Fri, 07 Feb 2020 08:43:16 GMT

Hello,

So what I have here is 1 table:

Item	Type	calendar.id	quantity
1	Sale	4000	10
1	Sale	4000	10
1	Purchase	4001	12
1	Purchase	4001	12
2	Sale	4002	14
2	Sale	4002	14
2	Purchase	4003	16
2	Purchase	4003	16

What I am trying to do is make a table with: 1) Item 2) Sale date 3) Order quantity

So "Sale date" is minimum date when sale took place (4000 for item 1 and 4002 for item 2) formula: date(MIN({<Type={'Sale'}>}calendar.id))

But I struggle to come up with Order quantity formula, I want to Sum quantity sold on that minimum date on each item.

What I came u with:

SUM({<calendar.id={'$(=MIN(calendar.id))'}, Type={'Sale'}>}quantity)

But calendar.id={'$(=MIN(calendar.id))'} ends up 4000 for both rows so it is correct for first line, but wrong for second one

I know I can do it by adding one more field in load script, but making it like this is what I need, thanks

P.S. Atached sample

Re: Dynamic Modifier in Set Analysis

RDBurmon — Fri, 07 Feb 2020 10:59:23 GMT

tbl:
LOAD
Item & '_' & calendar_id AS Key,
*
Inline
[
Item,Type,calendar_id,quantity
1,Sale,4000,10
1,Sale,4000,10
1,Purchase,4001,12
1,Purchase,4001,12
2,Sale,4002,14
2,Sale,4002,14
2,Purchase,4003,16
2,Purchase,4003,16
];

NoConcatenate
tbl1:
LOAD
Item & '_' & MIN(calendar_id) AS Key,
'Y' as Flag
Resident tbl
group by Item;

exit SCRIPT;

and then add straight table with below expression

=SUM({<Flag={'Y'}>}quantity)

Re: Dynamic Modifier in Set Analysis

luksse7 — Fri, 07 Feb 2020 11:02:11 GMT

hank you for your reply. That would work, but I need formula in chart, without any modification in script.

Re: Dynamic Modifier in Set Analysis

veidlburkhard — Fri, 07 Feb 2020 18:18:47 GMT

Hi Luksse7,

if you can't change your script, this could be your solution in your front end:

Your expression is related to the calculated dimension

If(calendar.id = Date(Aggr(Min(calendar.id), Item)), Sum(quantity))

this will give you the expected result (see the picture).

By the way, you can hide 'calendar.id' under the Presentation tab.

Hope this helps

Burkhard

Re: Dynamic Modifier in Set Analysis

Brett_Bleess — Thu, 05 Mar 2020 16:30:08 GMT

Did the last post get you a working solution? If so, do not forget to return to the thread and on that post, use the Accept as Solution button to mark it, which will give the poster credit for the help and let other Members know that worked for you. If you still need further assistance, please leave an update.

The only other area to which I can point you is the Design Blog, below are a couple of specific links and then I will put the general URL last in case you want to search that area further on your own.

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett