topic Re: Pivot table Value Wrongly calculates Interval in QlikView

Pivot table Value Wrongly calculates Interval

voxjungle — Sat, 16 Nov 2024 02:50:43 GMT

Hi,

I am trying to post a Sum of HOURS field in a pivot table & the HOURS field has values like HH:MM (60 minutes format)

But When i try to sum this in a Pivot table, The result value is shown as HH:MM (100 minutes format)

For example, sum of entries like 2.30 and 2.40 is giving me 4.80 but i expected it to be 5.10

Can you help me with correct format ?.

I tried to use interval function like =interval(Sum(HOURS)/24,'h.mm')) , but it's diving total by 24 instead of actual function.

Re: Pivot table Value Wrongly calculates Interval

sunny_talwar — Thu, 01 Aug 2019 12:21:53 GMT

Try this instead

=Interval(Sum(Interval#(HOURS, 'h.mm')), 'h.mm'))

Re: Pivot table Value Wrongly calculates Interval

voxjungle — Thu, 01 Aug 2019 13:21:27 GMT

@sunny_talwar That formula is again doing some division operation instead of formatting.

For example, It's converting 395.83 which is total Sum to 16.49

395.83/24 = 16.49

Re: Pivot table Value Wrongly calculates Interval

sunny_talwar — Thu, 01 Aug 2019 13:37:17 GMT

Not sure I understand... it is converting 395.83 to 16.49? But hard-coding it gives me a different value

Re: Pivot table Value Wrongly calculates Interval

voxjungle — Thu, 01 Aug 2019 14:14:35 GMT

Yes. It's dividing Sum by 24. Not sure why. May be it has something to do with Pivot table funcationality.

=Sum(HOURS) it shows me 395.83

But your formula is giving me 16.49

Re: Pivot table Value Wrongly calculates Interval

sunny_talwar — Thu, 01 Aug 2019 14:27:14 GMT

I don't know why you see 16.49... did you remove the division by 24? My expression didn't divide by 24... but you original expression did...

=Interval(Sum(Interval#(HOURS, 'h.mm')), 'h.mm'))

Re: Pivot table Value Wrongly calculates Interval

voxjungle — Sun, 04 Aug 2019 10:19:50 GMT

Hi,

I am not doing any division by 24. I am using the same formula as you proposed but it's internally dividing by 24 and producing wrong result.

It's kind of a bug it seems.

Re: Pivot table Value Wrongly calculates Interval

sunny_talwar — Mon, 05 Aug 2019 11:37:02 GMT

Are you able to share a sample where we can see the issue?

Re: Pivot table Value Wrongly calculates Interval

voxjungle — Tue, 06 Aug 2019 09:38:56 GMT

Please see the difference i get when i apply the formula.

If i just do a Sum, i get 50.** but when i apply formula it divides by 24 to give me 2.**

Re: Pivot table Value Wrongly calculates Interval

MarkWillems — Tue, 06 Aug 2019 12:33:43 GMT

Are the numbers you are using decimals and not a time format, so .5 will not be interpreted as 30 minutes.

Turn it into a round number with minutes as the denominator.

=Interval(sum(Interval#(Hours*60,'mm')),'hh:mm')

Otherwise can you post the background data please as a table.

Re: Pivot table Value Wrongly calculates Interval

voxjungle — Wed, 07 Aug 2019 06:20:02 GMT

@Anonymous, This is also not working for me. It gives me same result as with Sunny's formula

Re: Pivot table Value Wrongly calculates Interval

MarkWillems — Wed, 07 Aug 2019 08:02:50 GMT

OK, could you post the example qvw to take a look at?

Re: Pivot table Value Wrongly calculates Interval

voxjungle — Wed, 07 Aug 2019 08:49:07 GMT

PFA my qvw file

Re: Pivot table Value Wrongly calculates Interval

MarkWillems — Wed, 07 Aug 2019 09:39:53 GMT

Hey

There area three problems. Your expression is summing the hours before they are converted to a time. SO it will treat the .3 as a decimal and not a half hour. The sum needs to sit around the interval#

=Interval(Sum(Interval#(hours, 'h.mm')), 'h.mm')

Secondly, the format is wrong because you have set the format manually in the "Number" tab. The expression is forced to be Fixed at 2 decimal places, so it converts the number from a time format. Change it back to expression default.

Finally, your 10.3 will be read as 10 hours and 3 minutes, not 30 which I suspect it should be. You need to get your data collection to be a bit better, or affect the format of the data so that it properly reflects the time in a correct format.

So AB right now which has 5 sets of 10.3 hours reads as 50.15 hours and not 52.5. It may be that if there is only one number after the decimal place then you automatically add a 0 to it, but better you get the source data to be a proper time format.

Re: Pivot table Value Wrongly calculates Interval

voxjungle — Wed, 07 Aug 2019 10:54:19 GMT

Thanks. that helped

Re: Pivot table Value Wrongly calculates Interval

sunny_talwar — Wed, 07 Aug 2019 11:00:30 GMT

So, I guess my initially provided expression was not incorrect after all 🙂