# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Cloud Maintenance is scheduled between March 27-30. Visit Qlik Cloud Status page for more details.
cancel
Showing results for
Did you mean:
Contributor

## Summing up of interval data (in days) gives incorrect total

Hi all,

As shown below, i am able to find the right interval in days between two dates (Interval(date#(To,'MM/DD/YYYY')-date#(From'MM/DD/YYYY'), 'dd'). However, when I sum up those intervals (the last column called Duration in the table below), the Total Duration is much higher. It doesn't show the right total. Could someone please help me solve this problem? I tried already multiple solutions but nothing seems to give me the right total.

Labels (4)

• ### Set Analysis

5 Replies
Employee

If you want to calculate a sum per ID, you need to create a measure. I can tell from the search symbol in the "Duration" column that this is a dimension. And then it doesn't sum - it just shows unique values.

So, try

Sum(date#(To,'MM/DD/YYYY')-date#(From,'MM/DD/YYYY'))

Also, once you have a measure, the total is NOT the total of rows. It is the total disregarding the dimension(s). Depending on data model this sometimes makes a difference.

Contributor
Author

Hi hic,

Thanks for your reply. The total duration is the measure I created using exactly the expression you mention here.

It still gives the same number.

Employee

There are many things that could cause this...

If you select a single ID, is the result OK then?

How have you defined the measure in the KPI object?

Contributor
Author

If I select a single id it shows the correct number only for 1 one of these IDs (ending with 529). For the other two IDs, it shows incorrect numbers. My assumption is that when I sum the intervals it doesn't add the numbers shown in the duration column. I tried to force the difference in dates as a number with num function but that doesn't work either.

Employee

There is something wrong either in your data model, or in the aggregation in the KPI object.
- Add a measure "Count(To)" in your chart, to see how many dates you have for one ID.
- How have you defined the measure in the KPI object?

The Num() function wouldn't make any difference. It only formats the number - it doesn't change it. And it seems as if your duration is calculated properly, i.e. the dates are indeed interpreted as dates.

Tags
Community Browser