topic Re: How to create a set analysis expression in QlikView

How to create a set analysis expression

Tue, 29 Dec 2015 01:52:31 GMT

I am trying to create an expression where:

Count App ID, in spreadsheet Complete List; excluding field Exclusion =Yes from spreadsheet Partial List

and filters by Location =Arizona, Texas and Date > 01/06/2015

See attached spreadsheet

Re: How to create a set analysis expression

Anonymous — Tue, 29 Dec 2015 10:26:21 GMT

like this?

=count({<Date=, Location={'Arizona', 'Texas'}, Exclusion={'Yes'}, Date={'>$(=01/06/2015)'}>} APPID)

Make sure format of Date Field and 01/06/2015 should be same

Re: How to create a set analysis expression

PrashantSangle — Tue, 29 Dec 2015 10:31:30 GMT

Hi,

If you are not considering Exclusion = Yes

then use expression suggested by Balraj just write -

=count({<Date=, Location={'Arizona', 'Texas'}, Exclusion -={'Yes'}, Date={'>$(=01/06/2015)'}>} APPID)

Regards

Re: How to create a set analysis expression

tamilarasu — Tue, 29 Dec 2015 10:35:42 GMT

Hi Mauricio,

Based on your data,

=Count({< Location={'Arizona', 'Texas'}, Exclusion={'1'}, Date={">01/06/2015"}>} APPID)

Re: How to create a set analysis expression

Peter_Cammaert — Tue, 29 Dec 2015 10:42:10 GMT

Watch out guys.

Using $-sign expansion with an equal sign will treat everything after the equal sign as an expression. Meaning 1 divided by 6 divided by 2015 = 7 seconds into December 30, 1899

Re: How to create a set analysis expression

Tue, 29 Dec 2015 13:47:47 GMT

‌

Thank you Balraj, it works fine. However, when all records in column Exclusion = 1 (spreadsheet Partial List) the count is zero

I need to count the App ID from spreadsheet Complete List excluding fields Exclusion =1 (11 records) and using the filters as described earlier

Re: How to create a set analysis expression

Tue, 29 Dec 2015 14:12:00 GMT

Balraj thank you, it works fine. However, when all records in column Exclusion =1 (spreadsheet Partial List) the count is zero.

I need to count the App ID from spreadsheet Complete List excluding fields Exclusion = 1 (11 records) and use the filters as described earlier.