Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Did you mean:
Contributor III

Interval match, calculation of result, kpi

Hey everyone I'm sure someone has had this issue before.

Overview:

I have completed an interval match for a slow changing dimension - a shop rate. Making it span across the dates where the shop rate changes is where I'm having the issue. I am able to correctly calculate a total for within a table but when I try to translate that into a kpi it gives me a null value.

Detail:

Prior to Nov 1 2016 our shop rate was \$60

On Nov 1 2016 our shop rate is \$70

When I do a summary prior to Nov 1 2016 or after, I get the proper results. for example: 400hrs * 60 = 24,000 and 50hrs * 70 = 3,500, but if I try to consolidate the entire time into a kpi I get a null value. While clearly it should be 27,500.

I can get the correct value in a table but I can't get it the KPI.

Can anyone help me with this issue?

Update

Here is a couple screen shots to help clear up the issue. Note: In the example above I used generic numbers

No date filter set (spanning the shop rate change)

You can see that the cost is clearly calculated in the totals bar in the table but does not translate to the kpi.

Shop rate from Nov 1 2016 on:

Here you can see that when the shop rate is after the mix date it calculates the correct cost. (Also works the same prior to the Nov 1 change date)

Calculation for the KPI: sum(totaltime) * [Shop Rate]

1 Solution

Accepted Solutions
Contributor III
Author

I got the solution from my partner:

in this case use: ALT(QATimeTotal,0)

Here's the link to alt function: https://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/Scripting/ConditionalFunctions/a...