topic Re: set Analysis help in QlikView

set Analysis help

arusanah — Wed, 25 Nov 2020 16:16:04 GMT

Hi Team ,

I am trying to count records where price is 0 's or blank or nulls . I used below expression but it just returns data with price 0.

count({<PRICE ={0,' ' }>}distinct ORDER_ID)

how can I modify above expression to pull records for nulls and blanks rows too ?

kushalthakral — Fri, 26 Apr 2019 13:23:15 GMT

Hi Arusanah
Can you please post sample records so that i can help you on this

Thanks
Kushal

kushalthakral — Fri, 26 Apr 2019 13:31:29 GMT

You can use the follwing Set Expression

=Count({<Price = {0}>+<Price = {''}>}OrderID)
+
Count({1-<Price = {"*"}>}OrderID)

I hope this answers your question

Thanks

Kushal

trdandamudi — Fri, 26 Apr 2019 13:36:08 GMT

One way is as below:

=count({<PRICE ={0,"=Len(Price)=0" }>} distinct ORDER_ID)

sunny_talwar — Fri, 26 Apr 2019 13:36:17 GMT

Try this

Count({<ORDER_ID = {"=Sum(PRICE) = 0"}>} DISTINCT ORDER_ID)

Count({<PRICE ={0}>+<ORDER_ID = {"=Len(Trim(PRICE)) = 0"}>}DISTINCT ORDER_ID)

sunny_talwar — Fri, 26 Apr 2019 13:37:09 GMT

Or this

Count({<ORDER_ID = {"=Len(Trim(PRICE)) = 0 or PRICE = 0"}>} DISTINCT ORDER_ID)