Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
bitaneyim
Contributor

Aggr value between begin and end date

Hi Guys,

following issue.

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

contractbeginendtotal value
12301.01.201431.12.2018100 000
12401.01.201531.05.2016500 000
23410.03.201630.11.20196 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:

yearmonthtotal value
2015-05600 000
2016-066 100 000
2019-016 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

1 Solution

Accepted Solutions
bitaneyim
Contributor

Re: Aggr value between begin and end date

Hi Sunny,

I've solved it. Really simple

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

I think this is the best way.

Thank you

View solution in original post

17 Replies

Re: Aggr value between begin and end date

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

bitaneyim
Contributor

Re: Aggr value between begin and end date

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

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

bitaneyim
Contributor

Re: Aggr value between begin and end date

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

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)

bitaneyim
Contributor

Re: Aggr value between begin and end date

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

What expression are you using?

bitaneyim
Contributor

Re: Aggr value between begin and end date

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

Re: Aggr value between begin and end date

How about if you do this

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