topic Re: P() and e() in set analysis with condition in QlikView

P() and e() in set analysis with condition

Wed, 02 Aug 2017 14:38:46 GMT

i have the following sample data

by using the following expression:

=Count(DISTINCT{<Category = p({<Category, [Session ID] = p([Session ID]), Flag = {0}>}), Flag = {0}, [Session ID] = p({<Flag = {1}>})*e( {<Flag = {0}>})>}[Session ID])

i was able to achieve:

like below:

when selected category E at the list box,( considering all session_id that have the selected category) the chart count 1 for the category dimension when:

0 item of category E was sold, but the display category have>=1 items sold

but i cannot achieve the opposite like below:

when selected category E at the list box, considering all session_id that have the selected category)the chart count 1 for the category dimension when:

>=1 items of the selected category sold but 0 items of the display category was sold.

the result using the above data would be:

when selected E, the chart should show:

R:1( count from session 5555)

W:1( count from session 1111)

Q:1( count from session 1111)

when select DB, the chart should show:

E:1( count from session 3620)

Q:1( count from session 3620)

when select Q, the chart should show:

R: 1( count from session 5555)

Any one can help solve this problem.....???

Re: P() and e() in set analysis with condition

sunny_talwar — Wed, 02 Aug 2017 17:50:38 GMT

Are you sure that when E is selected, you are hoping to get

R:1( count from session 5555)

W:1( count from session 1111)

Q:1( count from session 1111)

and not

R:2( count from session 4444, 5555)

W:1( count from session 1111)

Q:1( count from session 1111)

JL:1( count from session 1111)

I got the above using this expression....

=Count(DISTINCT{<[Session ID] = p({<Flag = {0}>}[Session ID]), Flag = {1}, Category = e(Category)>}[Session ID])

But didn't know why R from 4444 and JL from 1111 not included?

Re: P() and e() in set analysis with condition

Thu, 03 Aug 2017 02:45:43 GMT

Hi, Sunny, thanks for your reply,

When E is selected R from 4444 is not include because there is an item R is sold in that session(the item id of the R category wirh flag=0 should have another item id say 290 to indicate that it is 2 different item from the same category)..

and also JL from 1111 is not include becaise there is another JL category sold in the same session....

count 1 when the selected category have>=1 items sold, and the displayed category have no items of that category sold.....

dont know if my description is clear enough....

Re: P() and e() in set analysis with condition

sunny_talwar — Thu, 03 Aug 2017 11:19:16 GMT

Got it, try this

=Count(DISTINCT{<[Session ID] = p({<Flag = {0}>}[Session ID]), Flag = {1}, Category = e(Category), Key = {"=Avg({<Category>}Flag) = 1"}>}[Session ID])

Where Key is created in the script like this

Table:

LOAD *,

[Session ID]& Category as Key,

If(Len(Trim(InvoiceID)) = 0, 1, 0) as Flag;

LOAD * INLINE [

Session ID, InvoiceID ,ItemID, Category,sesion_start,log_datetime

1111, 2, 219, E,2017-01-01 11:30:30, 2017-01-01 11:30:39

1111, 2, 220, DL,2017-01-01 11:30:31, 2017-01-01 11:30:29

Re: P() and e() in set analysis with condition

Fri, 04 Aug 2017 14:36:00 GMT

Sorry for late reply.

Thanks Sunny!

Re: P() and e() in set analysis with condition

smithaprabhakar — Mon, 31 Dec 2018 08:47:50 GMT

Can i get any solution for the below condition using P()

if(vAbsoluteThrshold=0,-1,fabs(sum({<$(='[$(premiumField)]')))>=vAbsolutethreshold)