topic Re: Straight Table Displaying null values in QlikView

Straight Table Displaying null values

ashokunbi — Fri, 20 Nov 2020 18:02:30 GMT

I have a straight table that I don't want displaying null values.I read a comment that said I can control the value I don't want displayed by making the dimension suppress null values and making expression values I don't want as null values so I have the expression below:

If(1-MAPE >.70,null(), 1-MAPE)

The column is displaying values less than 70% and setting values higher than 70% as null, -, but I don't want it to display the null values period. Below is an example of what I'm getting. Please help.

AvailableWeek	ABS Variation	MAPE	FCA
34	29	51.7%	48.3%
34	11	11.0%	-
34	9	35.0%	65.0%
34	19	50.9%	49.1%
34	143	43.3%	56.7%
34	48	75.0%	25.0%
34	0	100.0%	0.0%
34	6	16.6%	-
34	7	21.2%	-
34	3	27.7%	-

Re: Straight Table Displaying null values

sinanozdemir — Mon, 31 Aug 2015 16:36:05 GMT

Hi Ade,

This might be what you are looking for:

And then in the presentation tab, choose "Suppress Zero-Values":

Hope this helps

Re: Straight Table Displaying null values

ashokunbi — Mon, 31 Aug 2015 17:02:35 GMT

Sinan, Thanks for your help but that didn’t work. The null values are still displayed.

Re: Straight Table Displaying null values

Anonymous — Mon, 31 Aug 2015 17:36:09 GMT

Not exact but one option is to have MAPE as a expression with FCA.

Please see the the file attached.

Hope This will give some idea.

Re: Straight Table Displaying null values

sinanozdemir — Mon, 31 Aug 2015 19:13:41 GMT

Hi Ade,

Try to change the expression to the below:

And then in the presentation tab, check "Suppress Zero-Values".

Re: Straight Table Displaying null values

ashokunbi — Mon, 31 Aug 2015 19:42:11 GMT

Nope. It just turned all the values to 0%. Thanks for your help.

Re: Straight Table Displaying null values

sinanozdemir — Mon, 31 Aug 2015 19:48:11 GMT

Is your original dataset different than what you previously posted?

Can you post a bit more comprehensive dataset?

Re: Straight Table Displaying null values

ashokunbi — Mon, 31 Aug 2015 20:17:36 GMT

There is model column which I have added below (has no impact on the calculations, I think) but there are 3 other disabled columns that I used to get the values for the ABS Variation field. Available week and Model are dimension fields.

Re: Straight Table Displaying null values

sinanozdemir — Mon, 31 Aug 2015 20:19:45 GMT

So FCA isn't an expression, is a dimension, correct?

Re: Straight Table Displaying null values

ashokunbi — Mon, 31 Aug 2015 20:22:24 GMT

Week Available and Model are the only dimensions, all else are expressions.

Re: Straight Table Displaying null values

sinanozdemir — Mon, 31 Aug 2015 20:31:49 GMT

Without seeing the application, it is a bit difficult to determine why mine works, but not yours.

You can also handle this in the back-end:

And your data model will look like this:

Post your sample app, I cannot open it. However, someone else can open it and see what's wrong.

Re: Straight Table Displaying null values

Qrishna — Mon, 31 Aug 2015 20:32:07 GMT

See attached.

Re: Straight Table Displaying null values

Anonymous — Mon, 31 Aug 2015 20:41:25 GMT

You must not want to suppress 0.0% as a FCA value from the table. Isn't following model work? or you can try the expression sent by Krishna.

Re: Straight Table Displaying null values

ashokunbi — Mon, 31 Aug 2015 21:26:00 GMT

This is showing FCA over 70%. My values are not the issue, I just don’t want to display any FCA greater or equal to 70%

Re: Straight Table Displaying null values

Qrishna — Tue, 01 Sep 2015 16:31:27 GMT

is this what you wanted?

see attached

Re: Straight Table Displaying null values

ashokunbi — Thu, 03 Sep 2015 14:58:11 GMT

Thanks for your help. Sorry I couldn’t respond sooner. The suggestion is not working, it came up with the wrong values for FCA. I already have the correct values, I just don’t want to display any values greater than 70%.

I’m not sure if the issue is that you are using the value supplied in Mape as is (your expression for Mape is Mape & ‘%’) but it is actually the result of this expression:

if( > sum(RcvdQty), 1, /sum(RcvdQty))

I’m not sure if this helps.

Re: Straight Table Displaying null values

Qrishna — Mon, 12 Oct 2015 13:20:32 GMT

Any luck>