- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Chart sum of data packets by last date
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:
- The last date entered for Category A is 2/1/2020 and the sum of all Category A counts is 6
- The last date entered for Category D is 1/31/2020, and the sum of all Category D counts is 8
- Both of these "last dates" fall in the week whose Monday is 1/27/2020, so I want the chart to display a total of 14 on that week.
Likewise
- The last date entered for Category B is 2/5/2020, corresponding to the week whose Monday is 2/3/2020, so the sum of all Category B counts (13) will be displayed associated with the week of 2/3/2020
- The last date entered for Category C is 3/1/2020, corresponding to the week whose Monday is 2/24/2020, so the sum of all Category C (15) counts will be displayed associated with the week of 2/24/2020
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
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you. This worked.