Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi There,
I am new to Qlik and trying to visualize and create report where I need to compare the selected day (from Date) and compare this day sale with the previous week same day.
Data table, I have with columns like Date , Zone (North, South, East, West), Product (Apple, Banana, Oranges), Sale
Zone | North | (this is user selection) | ||||||||
Date | 06/07/2022 | (this is user selection, so need to extract day and 8 data points for selected day from past weeks) | ||||||||
Zone | Product | Day | 06/07/2022 | 29/06/2022 | 22/06/2022 | 15/06/2022 | 08/06/2022 | 01/06/2022 | 25/05/2022 | 18/05/2022 |
North | Apple | 4 | 67 | 88 | 99 | 98 | 98 | 0 | 67 | 0 |
North | Banana | 4 | 77 | 22 | 34 | 11 | 22 | 43 | 54 | 66 |
North | Oranges | 4 | 89 | 111 | 66 | 75 | 35 | 345 | 345 | 211 |
so, for each week, create a expression
sum({<Date={'$(=date(max(Date)))'}>} Value) /for actual week
sum({<Date={'$(=date(max(Date)-7))'}>} Value) /for last week
sum({<Date={'$(=date(max(Date)-14))'}>} Value) /for two weeks ago
and so on.
Regards
@kodekrackerz Please see the below code that I used first in the back end:
NoConcatenate
Temp:
Load *
Inline [
Zone, Product, Day, 06/07/2022, 29/06/2022, 22/06/2022, 15/06/2022, 08/06/2022, 01/06/2022, 25/05/2022, 18/05/2022
North, Apple, 4, 67, 88, 99, 98, 98, 0, 67, 0
North, Banana, 4, 77, 22, 34, 11, 22, 43, 54, 66
North, Oranges, 4, 89, 111, 66, 75, 35, 345, 345, 211
];
Temp2:
CrossTable(Date, Sales,3)
Load *
Resident Temp;
NoConcatenate
Temp3:
Load Zone,
Product,
Day,
Sales,
Date(Date#(Date,'DD/MM/YYYY'),'MM/DD/YYYY') as Date
Resident Temp2;
Drop table Temp, Temp2;
Exit Script;
Front End: You use 2 expressions:
Sum({<Date={"$(=Date(Max(Date),'MM/DD/YYYY'))"}>}Sales)
Sum({<Date={"$(=Date(Max(Date)-7,'MM/DD/YYYY'))"}>}Sales)