topic Re: qlik in App Development

qlik

prashantsanchet — Thu, 14 Dec 2017 11:59:51 GMT

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

i.e only null region sales

Re: qlik

Chanty4u — Thu, 14 Dec 2017 12:04:43 GMT

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

Re: qlik

sunny_talwar — Thu, 14 Dec 2017 12:52:03 GMT

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

Chanty4u — Thu, 14 Dec 2017 12:57:36 GMT

try this

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

Re: qlik

sunny_talwar — Thu, 14 Dec 2017 12:58:06 GMT

Not going to work

Re: qlik

Chanty4u — Thu, 14 Dec 2017 13:03:07 GMT

not tested sunny

Re: qlik

sunny_talwar — Thu, 14 Dec 2017 13:04:30 GMT

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

Chanty4u — Thu, 14 Dec 2017 13:05:38 GMT

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

Re: qlik

sunny_talwar — Thu, 14 Dec 2017 13:06:38 GMT

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

Re: qlik

Chanty4u — Thu, 14 Dec 2017 13:16:34 GMT

sure sunny

Re: qlik

Chanty4u — Thu, 14 Dec 2017 13:29:16 GMT

None of our expression is working:(

but below is working

script:

sample:

LOAD Product,

Category,

Type,

Sales,

Region

FROM

(ooxml, embedded labels, table is Sheet1);

FE:

Strighttable:

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

Exp:

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

Re: qlik

sunny_talwar — Thu, 14 Dec 2017 13:33:43 GMT

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

Re: qlik

Chanty4u — Thu, 14 Dec 2017 13:40:22 GMT

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

sunny_talwar — Thu, 14 Dec 2017 13:48:02 GMT

Can you share the qvw file?

Re: qlik

Chanty4u — Thu, 14 Dec 2017 13:53:19 GMT

PFA

Re: qlik

sunny_talwar — Thu, 14 Dec 2017 13:59:51 GMT

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