topic Re: Variable disappears when drill down in table chart in QlikView

Variable disappears when drill down in table chart

Wed, 20 May 2015 21:19:00 GMT

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.

sum({<RSD={'*'}>}MTD%COMPLETE)

Re: Variable disappears when drill down in table chart

ToniKautto — Thu, 21 May 2015 03:09:03 GMT

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.

=Num( (Column(1) / Column(2))-$(#v%Complete) ,'#,##0%')

Re: Variable disappears when drill down in table chart

Thu, 21 May 2015 22:45:46 GMT

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.

Thanks again!

Re: Variable disappears when drill down in table chart

ToniKautto — Thu, 21 May 2015 23:12:10 GMT

Would it be possible for your to provide a sample QVW file, so I can see your setup in more detail?

Re: Variable disappears when drill down in table chart

Fri, 22 May 2015 01:09:51 GMT

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.

Re: Variable disappears when drill down in table chart

Fri, 22 May 2015 01:11:36 GMT

Not sure if this message gets through to you. I replied with a sample file but the response shows as going to my original post, not to yours.

Re: Variable disappears when drill down in table chart

ToniKautto — Fri, 22 May 2015 01:55:14 GMT

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?

Re: Variable disappears when drill down in table chart

Fri, 22 May 2015 02:46:44 GMT

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.

Re: Variable disappears when drill down in table chart

ToniKautto — Fri, 22 May 2015 03:24:27 GMT

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.

Re: Variable disappears when drill down in table chart

Fri, 22 May 2015 03:33:48 GMT

Thanks for your quick replies. Will respond when I've tried your suggestions.

Re: Variable disappears when drill down in table chart

Fri, 22 May 2015 18:17:27 GMT

‌Right- there is no Region associated with MTD % because MTD % doesn't differ by Region or any other data field selections besides year, month and day. I want the selection of any field other than year, month or day to be ignored and not affect MTD % complete.

Thanks.

Re: Variable disappears when drill down in table chart

ToniKautto — Sat, 23 May 2015 00:30:04 GMT

I can understand that, but this is not the way the data is associated. So the data most be linked in a way that MTD is available for all regions. If the MTD value is unrelated to the data it can also be left in a data island, which means there is no connection to the other data. This way it would always remain unaffected by other selections. Which way you go to resolve the relation will depend on your data and how it relates. I am afraid I will not be able to give you e perfect answer for that.

Re: Variable disappears when drill down in table chart

Sat, 23 May 2015 01:06:53 GMT

Thanks again. I will respond once I fully review your suggestion.