topic Re: Value not shown when no sales (nested if expression) in QlikView

Value not shown when no sales (nested if expression)

Mon, 23 Dec 2013 08:53:02 GMT

Hey guys,

I have got the following dimension:

"group" = {group1, group2, group3}

each group as got its own budget: group1 = 1000; group2 = 10000; group3 = 20000

I do not want to touch the script or codes, so with the following nested expression I can show the budget for each group:

=if(group = group1, 1000,

//otherwise

if(group = group2, 10000,

//otherwise

if(group = group3, 20000

)))

(this is just a small version, I have got 10 "if's").

This works fine, BUT: There are no sales for group3 for the month of november 2013, so apparently it does not want to show me the budget of group3 either. It only shows "-".

I tried to untick "suppress values when zero", but this does not help.

The formula works fine in the month of October, since there are sales for each of the three groups, but since nothing of group3 has been sold in the month of November, it does now show me the budget...

How can I solve this problem?

Thanks for your help!

Re: Value not shown when no sales (nested if expression)

tresB — Mon, 23 Dec 2013 09:02:46 GMT

Could you please post a sample?

Re: Value not shown when no sales (nested if expression)

Mon, 23 Dec 2013 17:18:25 GMT

Plese see the attache qvw file which may help u.

Create 2 dimension and expression like below.

1. =ValueList('Group1','Group2','Group3')

2. =replace(grp,grp,'')

Expression:

1. =sum(if(ValueList('Group1','Group2','Group3')=grp,bud,0))

it should be straight table and un check suppress zero option and hide the un wantd colums.