topic Re: Catch value from previous week (cumulative measure) and use it in KPI in App Development

Catch value from previous week (cumulative measure) and use it in KPI

Micki — Sat, 16 Nov 2024 17:56:28 GMT

Hi guys, i have a cumulative measure

Week Start Date	Open items
2019-09-30	0
2019-10-07	2
2020-01-06	4
2020-01-13	6
2020-01-20	7
2020-02-03	8
2020-02-10	12
2020-02-17	21
2020-02-24	28
2020-03-02	33
2020-03-09	34
2020-03-16	36
2020-03-23	40
2020-03-30	42
2020-04-06	46
2020-04-13	54
2020-04-27	58
2020-05-04	63
2020-05-11	67
2020-05-18	75
2020-05-25	83
2020-06-01	87
2020-06-08	89
2020-06-15	93
2020-06-22	99
2020-06-29	104
2020-07-06	111
2020-07-13	117
2020-07-20	121
2020-07-27	123
2020-08-03	125
2020-08-10	132
2020-08-17	137
2020-08-24	142
2020-08-31	144
2020-09-07	151
2020-09-14	155
2020-09-21	169
2020-09-28	181
2020-10-05	189
2020-10-12	212
2020-10-19	219

Dimension:

WEEKSTART([Date])

Measure:

RangeSum(Above(TOTAL COUNT({<Status={'New', 'Active'}, [WorkItemType]={'Bug'}>}DISTINCT [%ID]), 1, RowNo(TOTAL)))

And it's working in table, but i need to catch value from previous week like: 2020-10-12 212,

and compare with current: 2020-10-19 219, and that comparison should be in KPI, so when i don't have week start dimension cumulative measure stop working and I don't know how to do it 😞

To summarize: my expected result should be two measures: one current which i have, and one from previous (cumulative measure) . Thank you in advance.

@sunny_talwar

Please Note, i am mostly interested how to do it in FrontEnd, right now i did it in BackEnd

Re: Catch value from previous week (cumulative measure) and use it in KPI

sunny_talwar — Wed, 21 Oct 2020 14:59:34 GMT

@Micki This should be easy to do using set analysis. May be this for 2020-10-19

Count({<Status = {'New', 'Active'}, [WorkItemType] = {'Bug'}, Date = {"<=$(=Date(Max(Date)))"}>} DISTINCT [%ID])

and this for 2020-10-12

Count({<Status = {'New', 'Active'}, [WorkItemType] = {'Bug'}, Date = {"<=$(=Date(WeekStart(Max(Date), -1)))"}>} DISTINCT [%ID])

Re: Catch value from previous week (cumulative measure) and use it in KPI

Micki — Thu, 22 Oct 2020 10:38:41 GMT

@sunny_talwar , OMG you change my mind how qlik is working :), thank you so much, never thought it can working in that way, i just made minor change for 2020-10-12:

Date(WeekStart(Max(Date), 2)