topic Sum of Values based on date field in App Development

Sum of Values based on date field

Arvind_07 — Sat, 16 Nov 2024 00:18:44 GMT

Hello all,

For the below table, I intend to use an KPI measure to show MTD Sales. But the MTD Sales should show the summary of values until last week. For getting the last week data, I use the expression

=sum({<Date={num(WeekStart(Today()-7))}>}[Total-FG.MTD Sales Units]).

But I know the above expression ain't right to get the correct value. I'm completely new to Qlik sense and have started learning.

Could you please help me with an expression to get the sum of MTD Sales for last week for the KPI measure

P.S. The Week numbers are linked to Sunday's date, that's why I have used week start function and also the Week numbers in my calendar and Qlik's are different as the only common calendar field is Date. Also on the above expression I tried to wrap the Date as number, because only then it is working due to the formats.

Thank you

Example-

Product Group	Week Number	Year	Month	Date	MTD Sales Units
A Box	W01 2020	2020	Jan 2020	2019-12-29	41
A Box	W02 2020	2020	Jan 2020	2020-01-05	146
A Box	W03 2020	2020	Jan 2020	2020-01-12	284
A Box	W04 2020	2020	Jan 2020	2020-01-19	410
A Box	W05 2020	2020	Jan 2020	2020-01-26	629
A Box	W06 2020	2020	Feb 2020	2020-02-02	169
A Box	W07 2020	2020	Feb 2020	2020-02-09	289
A Box	W08 2020	2020	Feb 2020	2020-02-16	533
A Box	W09 2020	2020	Mar 2020	2020-02-23	189

Re: Sum of Values based on date field

eddie_wagt — Tue, 06 Apr 2021 09:12:19 GMT

Hello @Arvind_07 ,

Maybe something like this

=sum({$<Date={"<=$(=date(WeekStart(max(Date)-7)))"}>}[Total-FG.MTD Sales Units])

=sum({$<Date={"<=$(=date(WeekStart(Today()-7)))"}>}[Total-FG.MTD Sales Units])

Regards Eddie

Re: Sum of Values based on date field

Arvind_07 — Tue, 06 Apr 2021 10:53:56 GMT

Thanks Eddie @eddie_wagt . For some reason it is still showing all the Dates values instead of last week's. Is there any way to sort this out

Re: Sum of Values based on date field

eddie_wagt — Tue, 06 Apr 2021 11:49:31 GMT

I read your requirements again, but do you need the sum of values for the past 7 days or do you need the sum of values of the previous week? Example: This week is week number 15 (202115) and previous week is (202114) . And I guess you want only show the sum of values of week 14