Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
luksse7
Partner - Contributor
Partner - Contributor

Dynamic Modifier in Set Analysis

Hello, 

So what I have here is 1 table:

ItemTypecalendar.idquantity
1Sale400010
1Sale400010
1Purchase400112
1Purchase400112
2Sale400214
2Sale400214
2Purchase400316
2Purchase400316

 

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

Capture.PNG

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

Capture1.PNG

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 

 

4 Replies
RDBurmon
Contributor II
Contributor II

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)

luksse7
Partner - Contributor
Partner - Contributor
Author

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

veidlburkhard
Creator III
Creator III

Hi Luksse7,

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

MinDate.jpg

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

Brett_Bleess
Former Employee
Former Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.