topic Re: Daily sum based on a period in QlikView

Daily sum based on a period

smiling_cheetah — Wed, 25 Nov 2020 16:16:04 GMT

Hi folks,

I got to calculate daily Sum of Value based on IDs available on each date. Basically initial table looks like:

ID	Value	StartDate	EndDate
1	100	01.01.2017	04.01.2017
2	110	01.01.2017	05.01.2017
3	120	02.01.2017	03.01.2017
4	130	01.01.2017	08.01.2017
5	140	03.01.2017	05.01.2017
6	150	06.01.2017	09.01.2017
7	160	07.01.2017	09.01.2017
8	170	05.01.2017	08.01.2017
9	200	08.01.2017	09.01.2017
10	300	03.01.2017	05.01.2017

And as the outcome I need to get:

Date	Value (Date>=StartDate and Date<= EndDate)
01.01.2017	340 (100+110+130)
02.01.2017	460
03.01.2017	900
04.01.2017	780
05.01.2017	850
06.01.2017	450
07.01.2017	610
08.01.2017	810
09.01.2017	510

Is it possible to do such calculation in visualization (not in the script?), and if not how can I incorporate this into the model (so when I choose a certain date, associated IDs which constitute the Value sum are selected) ?

Would appreciate any help

Re: Daily sum based on a period

Anil_Babu_Samineni — Tue, 06 Jun 2017 09:58:46 GMT

As per my experience it's not done in Visualization.

Re: Daily sum based on a period

smiling_cheetah — Tue, 06 Jun 2017 10:16:05 GMT

Yeah, so I think,

but I cant quite find a way to do it in the script either.

I've tried data island (like this post describes Cumulative distinct customer | Qlik Community) and it seems to work, but it's impossible to account for selections in this case

Re: Daily sum based on a period

PrashantSangle — Tue, 06 Jun 2017 10:18:43 GMT

I don't think so it is possible in front end.

but in back end you can achieve it using intervalmatch().

Regards,

Prashant Sangle

Re: Daily sum based on a period

sunny_talwar — Tue, 06 Jun 2017 11:10:36 GMT

May be using While or Interval Match.....

While based solution

Table:

LOAD *,

Date(StartDate + IterNo() - 1) as Date

While StartDate + IterNo() - 1 <= EndDate;

LOAD * INLINE [

ID, Value, StartDate, EndDate

1, 100, 01.01.2017, 04.01.2017

2, 110, 01.01.2017, 05.01.2017

3, 120, 02.01.2017, 03.01.2017

4, 130, 01.01.2017, 08.01.2017

5, 140, 03.01.2017, 05.01.2017

6, 150, 06.01.2017, 09.01.2017

7, 160, 07.01.2017, 09.01.2017

8, 170, 05.01.2017, 08.01.2017

9, 200, 08.01.2017, 09.01.2017

10, 300, 03.01.2017, 05.01.2017

];

Re: Daily sum based on a period

Anil_Babu_Samineni — Tue, 06 Jun 2017 12:54:30 GMT

May be typo error

While StartDate + IterNo() - 1 <= EndDate;

Re: Daily sum based on a period

Anil_Babu_Samineni — Tue, 06 Jun 2017 12:55:17 GMT

How you get it 900 for 3rd Date ??

Re: Daily sum based on a period

sunny_talwar — Tue, 06 Jun 2017 12:56:33 GMT

Hahahaha yes, thanks for pointing it out Anil

Re: Daily sum based on a period

sunny_talwar — Tue, 06 Jun 2017 12:57:46 GMT

Made the correction above

Re: Daily sum based on a period

smiling_cheetah — Tue, 06 Jun 2017 12:59:55 GMT

Thank you much, intervalmatch worked indeed

Re: Daily sum based on a period

smiling_cheetah — Tue, 06 Jun 2017 13:00:59 GMT

Thank you Sunny, tried both of those methods and both worked

Re: Daily sum based on a period

smiling_cheetah — Tue, 06 Jun 2017 13:04:27 GMT

it's sum of Values for IDs: 1, 2, 3, 4, 5, 10, so 100+110+120+130+140+300