topic Re: no zero-values in Formula in QlikView

no zero-values in Formula

Fri, 28 Nov 2014 12:49:22 GMT

Hello everybody,

I have a topic unfortunately with very much additional work in Excel for me. Maybe some of you can help me.

I have a table with values and then two additional red columns that calculate the difference between these two black columns

(one with positive amount, one with negative, but the value is the same, always).

Now I want to adapt that report.

Only the lines <> 0 in the red columns should appear. I know how to show only lines with value <> 0

But how can I only show lines with calculated result <> 0?

Re: no zero-values in Formula

bbi_mba_76 — Fri, 28 Nov 2014 13:36:30 GMT

Hi,

you could use an expression like this

if((column(1)-column(2))<>0, (column(1)-column(2)) )

or forcing a value in the else

if((column(1)-column(2))<>0, (column(1)-column(2)), '' )

Re: no zero-values in Formula

Gysbert_Wassenaar — Fri, 28 Nov 2014 13:39:11 GMT

You'll have to add the condition to all the expressions in your pivot table.

Re: no zero-values in Formula

Fri, 28 Nov 2014 13:42:43 GMT

Hi,

Let expr= Your Calculation.

In expression of Your Chart, Give the condition as

if(expr>0,expr).

Then Go to Object Properties--> Check Suppress Zero-Values

Regards,

Joshmi

Re: no zero-values in Formula

Anonymous — Fri, 28 Nov 2014 13:50:17 GMT

i think this is the only way unfortunately. but you do not want to hard code your condition though - i would create a hidden column with your condition expression which will evaluate to 1 or 0 and then use it for all your other expressions.

if (colHiddenExp > 1 , exp1)

if (colHiddenExp > 1 , expn)

Re: no zero-values in Formula

Fri, 28 Nov 2014 14:00:46 GMT

Does anybody know, if it is possible via set analysis.

This would be a solution that is easier for me

At the moment the formular is:

[IFRS AfA Per] - [HGB AfA Per]

Re: no zero-values in Formula

Mon, 01 Dec 2014 09:00:39 GMT

Hi,

thanks for you suggestions. I will try them and come back.

But does anyone know about a solution directly in the formular (without additional columns)?

Re: no zero-values in Formula

bbi_mba_76 — Mon, 01 Dec 2014 09:20:53 GMT

Sorry, but

if(([IFRS AfA Per] - [HGB AfA Per] )<>0, ([IFRS AfA Per] - [HGB AfA Per] ) )

is not working?

You have to do that for each expression as said by gwassenaar

Re: no zero-values in Formula

Mon, 01 Dec 2014 09:32:46 GMT

Hi Michele,

it works partially. Now the lines which have values are shown with values. The other dont show a value.

But the tricky challenge for me is that the empty lines are not shown anymore.

(From that Screenshot only the two lines with -3560,92 and 399,05 should stay in that pivot)

Thanks

Re: no zero-values in Formula

bbi_mba_76 — Mon, 01 Dec 2014 09:35:29 GMT

but you have to add the if in each expression, also for the first two columns:

if(([IFRS AfA Per] - [HGB AfA Per] )<>0, [IFRS AfA Per])

if(([IFRS AfA Per] - [HGB AfA Per] )<>0, [HGB AfA Per])