topic Re: Set Analysis Intersection in QlikView

Set Analysis Intersection

Mon, 27 Apr 2015 16:48:43 GMT

Hello,

i'm facing a big problem and i hope that somebody can help me.

I have a Data like

Month	ID	Flag1	Flag2
1	1	1
1	1		1
2	1	1
2	1		1
2	2	1
2	2		1
2	3	1
2	3		1
2	4	1

Now i want to count all IDs with Dimension Month where the ID has Flag1 = 1 and Flag2=1

For example: For Month 1, it should only count ID1 because only ID1 has a Row with Flag1 and a Row with Flag2 for given Month.

So Result should be for

Month 1: only ID 1

Month 2: ID1,ID2,ID3

Dimension Month	Expression Count IDs
1	1
2	3

I tried Intersection in Set Anaylsis but i didn´t work.

Pls can someone help me.

regards

steve

Re: Set Analysis Intersection

Anonymous — Mon, 27 Apr 2015 17:37:44 GMT

Try this:

=count (distinct {<ID = P ({<Flag1={"1"}>} ID ) >*<ID = P ({<Flag2={"1"}>} ID ) > } ID )

Thanks

Re: Set Analysis Intersection

ramoncova06 — Mon, 27 Apr 2015 17:43:09 GMT

count({<Flag1 = {'1'}, ID = P ({<Flag2={"1"}>}) >}ID)

Re: Set Analysis Intersection

maxgro — Mon, 27 Apr 2015 17:55:35 GMT

flag is '' or 1

you only want rows (by month and id) with flag1=1 and flag2=1, concat (&) is 11

this is the sum of the rows 11

sum(aggr(if(max(Flag1) & max(Flag2)=11,1,0), Month, ID))

Re: Set Analysis Intersection

Mon, 27 Apr 2015 18:29:33 GMT

try this

=count (distinct {<id = P ({<flag1={"1"}>} id ) >*<id= P ({<flag2={"1"}>} id ) > } id )

then output like this

Re: Set Analysis Intersection

Mon, 27 Apr 2015 19:02:18 GMT

thank you all for the quick answers!

it works

Re: Set Analysis Intersection

Anonymous — Mon, 27 Apr 2015 19:07:13 GMT

I have a table which has three fields Store_Name,Invoice_Number,Invoice_Date,Qty. Multiple store sale in different day . I want a report for single month sale with each day if store not sale a day the sale quantity will be zero. For example.

Table: A

Store_Name Invoice_Number Invoice_Date Qty

South City SI/2014-2015/3 1-JAN-15 10

North City SI/2014-2015/3 1-JAN-15 05

South City SI/2014-2015/4 3-JAN-15 03

South City SI/2014-2015/6 4-JAN-15 30.

South City SI/2014-2015/8 7-JAN-15 20.

North City SI/2014-2015/4 6-JAN-15 25

South City SI/2014-2015/10 17-JAN-15 20.

South City SI/2014-2015/12 22-JAN-15 50.

South City SI/2014-2015/14 25-JAN-15 5

South City SI/2014-2015/17 30-JAN-15 5

Result Will BE:

Store_Name Invoice_Number Invoice_Date Qty

South City SI/2014-2015/3 1-JAN-15 10

South City SI/2014-2015/3 2-JAN-15 00 -------> Zero (Here no sale)

South City SI/2014-2015/4 3-JAN-15 03

South City SI/2014-2015/6 4-JAN-15 30.

South City SI/2014-2015/6 5-JAN-15 00. -------> Zero (Here no Sale)

South City SI/2014-2015/6 6-JAN-15 00. -------> Zero (Here no Sale)

South City SI/2014-2015/8 7-JAN-15 20.

South City SI/2014-2015/6 8-JAN-15 00. -------> Zero (Here no Sale)

South City SI/2014-2015/6 9-JAN-15 00. -------> Zero (Here no Sale)

South City SI/2014-2015/6 10-JAN-15 00. -------> Zero (Here no Sale)

South City SI/2014-2015/6 11-JAN-15 00. -------> Zero (Here no Sale)

South City SI/2014-2015/6 12-JAN-15 00.

South City SI/2014-2015/6 13-JAN-15 00.

South City SI/2014-2015/6 14-JAN-15 00.

South City SI/2014-2015/6 15-JAN-15 00.

South City SI/2014-2015/6 16-JAN-15 00.

South City SI/2014-2015/10 17-JAN-15 20.

South City SI/2014-2015/6 18-JAN-15 00.

South City SI/2014-2015/6 19-JAN-15 00.

South City SI/2014-2015/6 20-JAN-15 00.

South City SI/2014-2015/6 21-JAN-15 00.

South City SI/2014-2015/12 22-JAN-15 50.

South City SI/2014-2015/6 23-JAN-15 00.

South City SI/2014-2015/6 24-JAN-15 00.

South City SI/2014-2015/14 25-JAN-15 5

South City SI/2014-2015/6 26-JAN-15 00.

South City SI/2014-2015/6 27-JAN-15 00.

South City SI/2014-2015/6 28-JAN-15 00.

South City SI/2014-2015/6 29-JAN-15 00.

South City SI/2014-2015/17 30-JAN-15 5

South City SI/2014-2015/6 31-JAN-15 00.

Any body please help me for solve this problems..........

Re: Set Analysis Intersection

ramoncova06 — Mon, 27 Apr 2015 19:14:47 GMT

I suggest you open a new thread for you question, that why you can get more people looking it

though here is a way to generate missing data

Generating Missing Data In QlikView