Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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

Tags (1)
1 Solution

Accepted Solutions
Highlighted

Re: Daily sum based on a period

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
Highlighted

Re: Daily sum based on a period

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

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)
Highlighted
Creator
Creator

Re: Daily sum based on a period

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

Highlighted

Re: Daily sum based on a period

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.
Highlighted

Re: Daily sum based on a period

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

Highlighted

Re: Daily sum based on a period

May be typo error

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

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)
Highlighted

Re: Daily sum based on a period

How you get it 900 for 3rd Date ??

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)
Highlighted

Re: Daily sum based on a period

Hahahaha yes, thanks for pointing it out Anil

Highlighted

Re: Daily sum based on a period

Made the correction above

Highlighted
Creator
Creator

Re: Daily sum based on a period

Thank you much, intervalmatch worked indeed