Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
hank you for your reply. That would work, but I need formula in chart, without any modification in script.
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
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