Chart sum of data packets by last date

[terrusie]

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 

[Brett_Bleess]

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

[terrusie]

@Brett_Bleess 

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

[Brett_Bleess]

@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

[Kushal_Chawda · Accepted solution]

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;

[terrusie]

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

[terrusie]

@Kushal_Chawda 

Thank you. This worked.