sum desperately empty?

[Former Member]

Hello,

I have a report in webintelligence which goal is to compute the length items are loaned (in an academic library context) which is quite simple:

- one query to get the loan ID and the time it was issued

- one query to get the loan ID and the time it was returned

I have merged the two loan ID, created a dimension to compute the duration of each loan in minutes by subtracting the return date and the issue date. To get this working, in my table I had to select the Avoid duplicate row aggregation", maybe it explains the issue I experience.

I am able to get a table:

Loan ID	Issued	Returned	Duration
302032	2015/10/01 08:43	2015/10/02 08:27	1,424
302095	2015/10/01 10:59	2015/10/01 12:08	68

This part works fine, unfortunately, if I try to add a row to compute the sum of all the duration =Sum([DurationInMinutes]) the cell containing this fomula remains empty. I have tried to transform to numbers and things like that but could never get the things working.

Has anybody got an idea what I should do to get this done?

Thanks for any advice on this!

Sylvain

[Former Member]

Hi Sylvain,

Can you share the formula used to calculate [DurationsInMinutes]?

Regards,

Yuvraj

[Former Member]

Hi Yuvraj,

I have a lot of different variables so start by the bottom:

MinutesBetween (dimension)

=Floor(([SecondsBetweenNoError]/60))

SecondsBetweenNoError (dimension)

=If(IsError([SecondsBetween]);0;[SecondsBetween])

SecondsBetween (dimension)

=DaysBetween([Variables].[Issue];[Variables].[Discharge]) * 86400

+

(

   ToNumber(FormatDate([Variables].[Discharge];"HH")) * 3600 +

   ToNumber(Left(FormatDate([Variables].[Discharge];"mm:ss");2)) * 60 +

   ToNumber(FormatDate([Variables].[Discharge];"ss"))

)

-

(

   ToNumber(FormatDate([Variables].[Issue];"HH")) * 3600 +

   ToNumber(Left(FormatDate([Variables].[Issue];"mm:ss");2)) * 60 +

   ToNumber(FormatDate([Variables].[Issue];"ss"))

)

[Variables].[Issue] (Dimension)

=[Issue].[Transaction date]

[Variables].[Discharge] (Dimension)

=[Discharge].[Transaction date]

I hope it makes sense, I have added intermediate variables to try to track down the origin of the issue but have not been able to find anything for the moment.

Thanks for any advice,

Sylvain

[Former Member]

Hi Sylvain,

Thanks for the details.

Can you try using below formula and check:

=ForceMerge( Sum([DurationInMinutes])  )

Regards,

Yuvraj

[Former Member]

Hi Yuvraj,

thanks for the suggestion, unfortunately it still shoas as empty. I suspect there is something wrong with any of the data I try to sum but I am wondering if there is any "debug" option in Webi where I could get more information to find out what is going wrong with this operation?

• =ForceMerge( Sum([DurationInMinutes])  ) → empty celle
• =ForceMerge( Sum([DurationInMinutes] + 1)  ) → empty cell
• =ForceMerge( Sum(1 + 1)  ) → value outputed

So there is definitely an issue with my variable, need to find out what...

If you have any other idea, they are welcome!

[Former Member]

Hi Sylvain,

I tried to reproduce it with single data provider and was able to get the data; therefore, I am suspecting the merging somehow.

What do you see if you create a measure as Sum([DurationInMinutes])  and simply drag it on the report as a free standing cell (outside the report block)?

Does it show any value or error or come as blank?

Regards,

Yuvraj

[amitrathi239]

Hi,

Try with right click on the column->Insert->Sum.

Amit

[Former Member]

Hi Amit,

this is what I had tried first (well, using the toolbar but it seems to do the same thing) and this did not work. I have then tried to change the formula (adding useMerged and other things following suggestions found on the web, but nothing fixed my issue...)

Thanks for the help,

Sylvain