Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
smiling_cheetah
Creator
Creator

Daily sum based on a period

Hi folks,

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

IDValueStartDateEndDate
110001.01.201704.01.2017
211001.01.201705.01.2017
312002.01.201703.01.2017
413001.01.201708.01.2017
514003.01.201705.01.2017
615006.01.201709.01.2017
716007.01.201709.01.2017
817005.01.201708.01.2017
920008.01.201709.01.2017
1030003.01.201705.01.2017

And as the outcome I need to get:

DateValue (Date>=StartDate and  Date<= EndDate)
01.01.2017340 (100+110+130)
02.01.2017460
03.01.2017900
04.01.2017780
05.01.2017850
06.01.2017450
07.01.2017610
08.01.2017810
09.01.2017510

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

1 Solution

Accepted Solutions
sunny_talwar

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

];

Capture.PNG

View solution in original post

11 Replies
Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
smiling_cheetah
Creator
Creator
Author

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
sunny_talwar

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

];

Capture.PNG

Anil_Babu_Samineni

May be typo error

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anil_Babu_Samineni

How you get it 900 for 3rd Date ??

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sunny_talwar

Hahahaha yes, thanks for pointing it out Anil

sunny_talwar

Made the correction above

smiling_cheetah
Creator
Creator
Author

Thank you much, intervalmatch worked indeed