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

[vikasg]

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.

[hic]

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.

[vikasg]

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.

[hic]

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?

[vikasg]

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.

[hic]

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.