Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am new on Qlik Sense and If anyone can help me with the following situation:
I have the following table:
Week | Date | Amount |
---|---|---|
2016/31 | 01-08-2016 | 1219 |
2016/30 | 31-07-2016 | 1205 |
2016/30 | 30-07-2016 | 905 |
2016/30 | 29-07-2016 | 755 |
2016/30 | 28-07-2016 | 640 |
2016/30 | 27-07-2016 | 1355 |
2016/30 | 26-07-2016 | 1548 |
2016/30 | 25-07-2016 | 1520 |
2016/29 | 24-07-2016 | 1520 |
2016/29 | 23-07-2016 | 1357 |
2016/29 | 22-07-2016 | 1049 |
2016/29 | 21-07-2016 | 1005 |
2016/29 | 20-07-2016 | 697 |
2016/29 | 19-07-2016 | 654 |
2016/29 | 18-07-2016 | 249 |
2016/28 | 15-07-2016 | 246 |
Where the amount field is a Count (distict ID_pallet). I need to count the quantities for weeks for the last day on record of each week. I mean:
Week | Date | Amount |
---|---|---|
2016/31 | 01-08-2016 | 1219 |
2016/30 | 31-07-2016 | 1205 |
2016/29 | 24-07-2016 | 1520 |
2016/28 | 15-07-2016 | 246 |
I can't do it directly on the script because i also need the day for other analysis.
Beforehand thank you very much.
May be like this:
Week as dimension:
Expression:
Sum(If(Date = WeekEnd(Date), Amount))
Or may be using this script, create flags:
Table:
LOAD "Week",
"Date",
Amount
FROM [lib://Lib]
(html, codepage is 1252, embedded labels, table is @1);
FinalTable:
LOAD *,
If("Week" <> Previous("Week"), 1, 0) as Flag
Resident Table
Order By "Date" Desc;
DROP Table Table;
And then a table like this:
Dimension:
Week
Date
Expression:
Sum({<Flag = {1}>}Amount)
Thanks Sunny, but i have a problem with this method because in the last week ( this week) i have to show the last day available, for example for this week i have to show today amount and i dont have a change of week in this case. Can you help me?
If you reload the application today with today's data in it, then the flag will be reassigned to today's date. Have you tried reloading with new data?
Yes, you are right! thank you very much