topic Set Analysis - Match 2 conditions in QlikView

Set Analysis - Match 2 conditions

Fri, 04 Mar 2011 09:21:55 GMT

Hi all,

I have a problem! [:(]

I want to count the customers that have sales in two different season.

My issue is to count the customers that have sales in current season and also in the previous season but with order date <= (today()-365), that is to say customers that have sales in the last year until today()-365).

To explain better: today is 3rd March 2011. What I need is to count distinct customers that have sales in current season and also in previous season but with order date <= 3rd March 2010.

I try this with set analysis (i have two variables, s1 and s2, to indicate respectively current season and previous season).

I try this formula but it does not work:

count({$<[Season]={$(s1)}>*<[Season]={$(s2)}, [Order Date]={"<$(=date(setdateyear(today(),year(today())-1),'YYYYMMDD'))"}>}distinct [Customer])

Could someone help me?

Thanks a lot!

Set Analysis - Match 2 conditions

stephencredmond — Fri, 04 Mar 2011 10:13:50 GMT

Hi,

Don't overcomplicate your life!

Count({set1} Customer) + Count({set2} Customer)

Regards,

Stephen

Set Analysis - Match 2 conditions

Fri, 04 Mar 2011 11:07:14 GMT

Stephen,

this is not what i need! I need the intersection of customer of set1 and set2, not the union.

Set Analysis - Match 2 conditions

Fri, 04 Mar 2011 11:34:03 GMT

Try with this Method ,

In the Below Example Expression I have shown ' Premium Count ' , ' Product ' Wise for the year 2010 and 2009.

EXPRESSION : // Created 2 different expressions for year 2010 & 2009 and showed them in single BAR GRAPH.

2010 :

=sum({$<YearFin={$(vThisYear)}, MonthNum={"<=$(vThisMonth)"}>} CNT_MTD_NB)

2009:

=sum({$<YearFin={$(vPYear)}, MonthNum={"<=$(vThisMonth)"}>} CNT_MTD_NB)

NOTE:

vThisYear : It is a variable created to show current year as on date.

vThisMonth : It is a variable created to show current month.

vPYear : It is a variable created to show previous year as on date.

Regards

Bhargav Raj

Set Analysis - Match 2 conditions

Anonymous — Fri, 04 Mar 2011 11:40:36 GMT

Hi, elenagirardin,

I prefer, that Seasons are related to [Order Date]. If so, than you could manipulate only with dates.

Try to create 4 additional variables: vSeason1Start, vSeason1End, vSeason2Start, vSeason2End. You can put an expression in these variables to calculate correct end and start dates in format you need. Then your Set Analysis expression could look like:


Count({<[Order Date]={">=$(#vSeason1Start)<=$(#vSeason1End)"} * {">=$(#vSeason2Start)<=$(#vSeason2End)"}>} Distinct [Customer])

Add single quotes for variables if you have Order dates in string.

Rgds,
AT

Set Analysis - Match 2 conditions

Fri, 04 Mar 2011 13:52:02 GMT

Thanks a lot AT.

Unfortunately the Order Date is not related to Season....

Set Analysis - Match 2 conditions

Anonymous — Fri, 04 Mar 2011 14:46:26 GMT

What a pity!

Than it looks like this case is little bit complicated.

Set Analysis - Match 2 conditions

stephencredmond — Fri, 04 Mar 2011 15:03:46 GMT

Hi,

Sorry, my misunderstanding.

You need the intersection of referential sets - e.g.:

Sum({<Country=P({<Month={"Jan"}>} Country)*P({<Month={"Feb"}>} Country)>} Sales)

This gives me the Countries that both sales in January AND sales in February.

Regards,

Stephen