topic Re: Aggr value between begin and end date in QlikView

Aggr value between begin and end date

tiklabaq — Tue, 20 Feb 2018 16:23:29 GMT

Hi Guys,

following issue.

I want to show in a chart the value of contracts. These data are given:

contract	begin	end	total value
123	01.01.2014	31.12.2018	100 000
124	01.01.2015	31.05.2016	500 000
234	10.03.2016	30.11.2019	6 000 000

It's a bar chart with yearmonth as dimension. In this chart I want to see the total value of contracts per month based on begin and end date.

for example:

yearmonth	total value
2015-05	600 000
2016-06	6 100 000
2019-01	6 000 000

So the value should not be cumulated per day or month, rather show only the total value for all contracts which are valid in this period.

Do you have an idea

Re: Aggr value between begin and end date

sunny_talwar — Tue, 20 Feb 2018 16:30:14 GMT

Are you using IntervalMatch in your script to link month-year to begin and end?

Re: Aggr value between begin and end date

tiklabaq — Tue, 20 Feb 2018 16:35:07 GMT

Hi Sunny,

No, I do not do that because I do not want to generate unnecessary data

Re: Aggr value between begin and end date

sunny_talwar — Tue, 20 Feb 2018 16:41:06 GMT

Oh, so the two tables are not linked to each other on anything?

Re: Aggr value between begin and end date

tiklabaq — Tue, 20 Feb 2018 16:46:15 GMT

Yes, exactly, I thought we could solve this without intervalmatch?

And suppose I did it with intervalmatch, how do I display each value only once a month or a year?

Re: Aggr value between begin and end date

sunny_talwar — Tue, 20 Feb 2018 16:48:42 GMT

1st you don't have to use interval match, but a Cartesian Product on the front end which will use an if statement is not going to give you a good performance if you have a lot of data to work with

2nd you can intervalmatch with the monthstart rather than all dates and this should keep a single value for each month (I believe)

Re: Aggr value between begin and end date

tiklabaq — Tue, 20 Feb 2018 16:55:42 GMT

no, there are not so many data, with IF I have also got good results, but if I use as dimension date and not month. Because at months it accumulates the value in the chart for each day and displays the sum of every single day.

Re: Aggr value between begin and end date

sunny_talwar — Tue, 20 Feb 2018 17:02:13 GMT

What expression are you using?

Re: Aggr value between begin and end date

tiklabaq — Tue, 20 Feb 2018 17:04:16 GMT

sum(if(Date >= begin and Date <= end, total value))

Re: Aggr value between begin and end date

sunny_talwar — Tue, 20 Feb 2018 17:07:25 GMT

How about if you do this

Sum(If(Date >= begin and Date <= end and Date = MonthStart(Date), total value))

Re: Aggr value between begin and end date

tiklabaq — Tue, 20 Feb 2018 17:29:47 GMT

ok this is good. and how can I make it, if I want to see it in years?

Also, the month is inaccurate if the contract does not start on the 1st, but that's not so bad. if I do that with year, then it does not work. I tested it with yearstart(Date) or yearend.

Re: Aggr value between begin and end date

sunny_talwar — Tue, 20 Feb 2018 17:39:55 GMT

May be this

Sum(If(Date >= MonthStart(begin) and Date <= MonthStart(end) and Date = MonthStart(Date), total value))

or this

Sum(If(Date >= MonthStart(begin) and Date <= MonthEnd(end) and Date = MonthStart(Date), total value))

For Year, same logic

Sum(If(Date >= YearStart(begin) and Date <= YearStart(end) and Date = YearStart(Date), total value))

Sum(If(Date >= YearStart(begin) and Date <= YearEnd(end) and Date = YearStart(Date), total value))

Re: Aggr value between begin and end date

tiklabaq — Tue, 20 Feb 2018 17:57:53 GMT

it works perfect, but as you said, it is extremely slow

Will it be faster with set analysis?

Re: Aggr value between begin and end date

sunny_talwar — Tue, 20 Feb 2018 18:05:28 GMT

Set analysis will not work because the tables are not connected and hence I suggest Interval Match

Re: Aggr value between begin and end date

tiklabaq — Tue, 20 Feb 2018 18:09:15 GMT

If you work on it for hours, you lose the overview at some point. Of course I realized that, I think the best solution will be intervalmatch, you're right. And how do I solve this with year and month?

Re: Aggr value between begin and end date

sunny_talwar — Tue, 20 Feb 2018 18:15:53 GMT

Well, I would not worry about Year for right now and will focus on Interval Matching on MonthStart field (or MonthStart for Begin and MonthEnd for End)... once you have this, Year can be handled on the front end... can't really imagine how, but once you have Month working and can share you expression... I might be able to tell you how to make it work for year....

Re: Aggr value between begin and end date

tiklabaq — Wed, 21 Feb 2018 06:57:42 GMT

Hi Sunny,

I've solved it. Really simple

intervalmatch with date
expression: sum(value) / count(distinct date) --> result is the avg per dimension
now the chart shows the right values for month, year and so on

I think this is the best way.

Thank you

Re: Aggr value between begin and end date

sunny_talwar — Wed, 21 Feb 2018 09:59:51 GMT

I think so too...

I am glad you managed to get an answer... now would you mind closing this thread by marking correct and helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny