Hey everyone I'm sure someone has had this issue before.
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.
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?
Thank you in advance
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]
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/alt.htm