Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis Intersection

Hello,

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

I have a Data like

Month
IDFlag1Flag2
111
111
211
211
221
221
231
231
241

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 MonthExpression Count IDs
11
23

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

Pls can someone help me.

regards

steve

1 Solution

Accepted Solutions
padmaja_gade
Contributor III
Contributor III

Try this:

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

Thanks

View solution in original post

7 Replies
padmaja_gade
Contributor III
Contributor III

Try this:

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

Thanks

View solution in original post

ramoncova06
Specialist III
Specialist III

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

maxgro
MVP
MVP

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

1.png

Not applicable
Author

hi

try this

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

then output like this

Not applicable
Author

thank you all for the quick answers!

it works

dilipmantu
Contributor
Contributor

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
Specialist III
Specialist III

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