Variable disappears when drill down in table chart
I have a variable defined as: v%Complete=MTD%COMPLETE
It's used in a table chart with the expression:
Num(Column(1) / Column(2),'#,##0%')-$(v%Complete)
The table chart has a drillable group. The variable and expression above work fine at the top group but the variable disappears after drill down. Any suggestions or advice is welcome. Thanks!
P.S. I also tried defining the variable using set analysis and that completely zeroed out the variable.
You variable refers to a field without an aggregation function. This means that if you have more than one possible values in the field, QlikView does not know which one you expect to get back. In such case the variable will get the value NULL, which is displayed by a dash. This is identical to the functionality of Only().
v%Complete = Only ( MTD%COMPLETE )
If the variable is expected to have a value, you need to define a aggregation that returns the expected value, for example Count() Sum() Max() or Min().
I would suggest that you change your expression slightly, to properly apply the numeric format. So first do your aggregation, and then apply the format on the result. Also notice that I added a # sign in the dollar expansion, to ensure that you expand a numerical value from your variable.
Thanks you for a thorough explanation to go along with the solution. The formulas you suggested works on the first level of the group in the chart table but still disappears when I drill down. To simplify my example, I'm using
=(vMTDCalc) to return a variable used in calculations on a straight table. The correct number displays at the top of my group/hierachy, but when I drill down a level, the amount zeros out.
Attached is the sample qvw. Please note the % of complete file, based on a variable, zeros out when drilling down. R9 row to drill down on and see the issue. Thanks.
I think you might have reduced the sample a bit too much. The monthly quota is zero in the chart, so the % column becomes NULL since you can not have have a 0 denominator in a fraction.
Is this what happens when you drill down as well, that the quota turns into zeros?
My main issue at this point is the % Complete column going to 0 or NULL whenever I drill down. I feel pretty confident the other formulas are not working due to that issue. If I can resolve the % Complete , I can likely make the other expressions work. Thanks.
If you add MTDCOMP as a list box, you will see that it all becomes excluded when you change dimension. The % Complete can becomes NULL as there is no available value.
Looking at your data model, I think you should take a look at the data that you are loading. There are several rows that are complete NULL value rows, which means they add no logical value to the data.
When you think each individual table looks accurate, re validate that the keys link the data in a way that you consider accurate. For example there are no MTDCOMP value associated with the region R9.
