Set Analysis Intersection

[#1]

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

[Anonymous · Accepted solution]

Try this:

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

Thanks

[ramoncova06]

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

[maxgro]

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))

[#5]

hi

try this

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

then output like this

[#6]

thank you all for the quick answers!

it works

[Anonymous]

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..........

[ramoncova06]

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