Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello.
I am needing help wrapping my brain around how to chart summary data by the last date of the category.
For example:
I would like to chart the sum of [Count] for each category by week, but I want the total only associated with the last week in each category.
Category | Date | Count |
A | 1/1/2020 | 1 |
A | 1/2/2020 | 2 |
A | 2/1/2020 | 3 |
B | 1/2/2020 | 7 |
B | 1/19/2020 | 4 |
B | 2/5/2020 | 2 |
C | 2/4/2020 | 5 |
C | 2/7/2020 | 3 |
C | 3/1/2020 | 7 |
D | 1/27/2020 | 2 |
D | 1/29/2020 | 4 |
D | 1/31/2020 | 2 |
Ideally the output would be:
Week start date | Count Sum |
1/6/2020 | 0 |
1/13/2020 | 0 |
1/20/2020 | 0 |
1/27/2020 | 14 |
2/3/2020 | 13 |
2/10/2020 | 0 |
2/17/2020 | 0 |
2/24/2020 | 15 |
By way of explanation:
Likewise
Charting this simply by week, splits up the categories. I simply want the total sum to be associated with the week in which the category was last logged.
Sincerely,
Terry
Charting this simply by week, splits up the categories. I simply want the total sum to be associated with the week in which the category was last logged.
Sincerely,
Terry
Data:
LOAD Category,
date(Date) as Date,
Count
FROM Source;
AllWeeks:
LOAD WeekStart(Date,0,0) as WeekStart1;
LOAD *,date(MinDate+IterNo()-1) as Date
While MinDate+IterNo()-1<=MaxDate;
load min(Date) as MinDate,
max(Date) as MaxDate;
LOAD FieldValue('Date',RecNo()) as Date
AutoGenerate FieldValueCount('Date');
Left Join(Data)
LOAD Category,
date(max(Date)) as MaxDate_Category
Resident Data
Group by Category;
Final:
NoConcatenate
LOAD *,
WeekStart(MaxDate_Category,0,0) as WeekStart
Resident Data;
DROP Table Data;
Concatenate(Final)
LOAD WeekStart1 as WeekStart
resident AllWeeks
Where not Exists(WeekStart,WeekStart1);
DROP Table AllWeeks;
DROP Fields MaxDate_Category;
I think the lack of replies is due to your output not being explained well enough, as I have not been able to figure out how you got your numbers there, so if you can expand upon how that output is reached, that may help folks come up with some ideas at that point, but right now, I suspect everyone else is as confused as am I, so really need some further clarification in order to help.
Regards,
Brett
Thanks for the feedback. I hope my edit clears things up. Please let me know if anything remains ambiguous. I also noticed that I did not account for the fact that Qlikview defaults to Monday as being the starting day of the week with regard to charts.
Sincerely,
Terry
@sunny_talwar , @Kushal_Chawda , @Vegar , @Taoufiq_Zarra I know I left out a bunch of folks, but hoping one of you can have a look at this one now to see if you have any ideas on how to approach things, thanks in advance for the help on this one.
Cheers,
Brett
Data:
LOAD Category,
date(Date) as Date,
Count
FROM Source;
AllWeeks:
LOAD WeekStart(Date,0,0) as WeekStart1;
LOAD *,date(MinDate+IterNo()-1) as Date
While MinDate+IterNo()-1<=MaxDate;
load min(Date) as MinDate,
max(Date) as MaxDate;
LOAD FieldValue('Date',RecNo()) as Date
AutoGenerate FieldValueCount('Date');
Left Join(Data)
LOAD Category,
date(max(Date)) as MaxDate_Category
Resident Data
Group by Category;
Final:
NoConcatenate
LOAD *,
WeekStart(MaxDate_Category,0,0) as WeekStart
Resident Data;
DROP Table Data;
Concatenate(Final)
LOAD WeekStart1 as WeekStart
resident AllWeeks
Where not Exists(WeekStart,WeekStart1);
DROP Table AllWeeks;
DROP Fields MaxDate_Category;
Kush,
Thank you for your reply. This approach looks like it will work for me. Apologies in advance, that it may be couple days before I post the results.
Do you, by any chance, know whether this can be done from within a chart expression? I imagine it would be difficult, if not impossible, because it requires crossing table rows.
Sincerely,
Terry
Thank you. This worked.