15 Replies Latest reply: Dec 14, 2017 8:59 AM by Sunny Talwar

qlik

How to write set analysis to get sum of sales for region where region is null;

i.e only null region sales

• Re: qlik

sum({<region={" "}>}sales)

• Re: qlik

May be flag the null region in the script like this

If(Len(Trim(Region)) = 0, 1, 0) as NullRegionFlag

and then this

Sum({<NullRegionFlag = {1}>}Sales)

• Re: qlik

try this

sum({\$<region={"=isnull(region)"}>} sales)

• Re: qlik

Not going to work

• Re: qlik

not tested sunny

• Re: qlik

I am letting you know that it is not going to work.... and the reason is that you can't really compare a null field value within set analysis..... if it was white space, this would have worked

sum({\$<region={"=Len(Trim(region)) = 0"}>} sales)

• Re: qlik

Nice sunny let me try with the dummy data for my understanding ....

• Re: qlik

Sounds like a plan... share the sample afterwards as it might help someone else

• Re: qlik

sure sunny

• Re: qlik

None of our expression is working:(

but below is working

script:

sample:

Category,

Type,

Sales,

Region

FROM

[D:\New folder\sample.xlsx]

(ooxml, embedded labels, table is Sheet1);

FE:

Strighttable:

dim: Region   [you can change according to your req]

Exp:

sum({\$-<Region=-{}>}Sales)

• Re: qlik

You mentioned that non of "our" expression worked? Where did you try my method of creating a flag?

• Re: qlik

sorry sunny   it will work   i just tested with expressions of yours and mine of

sum({\$<region={"=Len(Trim(region)) = 0"}>} sales)

sum({\$<region={"=isnull(region)"}>} sales)

• Re: qlik

Can you share the qvw file?

• Re: qlik

PFA

• Re: qlik

So you meant that it did not work... but the flag will work