Daily sum based on a period

[smiling_cheetah]

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

[Anil_Babu_Samineni]

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

[smiling_cheetah]

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

[PrashantSangle]

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

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

Regards,

Prashant Sangle

[sunny_talwar · Accepted solution]

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

];

[Anil_Babu_Samineni]

May be typo error

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

[Anil_Babu_Samineni]

How you get it 900 for 3rd Date ??

[sunny_talwar]

Hahahaha yes, thanks for pointing it out Anil

[sunny_talwar]

Made the correction above

[smiling_cheetah]

Thank you much, intervalmatch worked indeed