Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis - Match 2 conditions

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!

7 Replies
stephencredmond
Luminary Alumni
Luminary Alumni

Hi,

Don't overcomplicate your life!

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

Regards,

Stephen

Not applicable
Author

Stephen,

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

Not applicable
Author

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

Anonymous
Not applicable
Author

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

Not applicable
Author

Thanks a lot AT.

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

Anonymous
Not applicable
Author

What a pity!

Than it looks like this case is little bit complicated.

AT

stephencredmond
Luminary Alumni
Luminary Alumni

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