Set Analysis with multiple conditions

[Xerxes]

My old set analysis skills are lost.

I have tried so many versions of below code without success.

Below version gives me the correct count for the date but does not apply the other modifiers.

Count(Distinct ${<import_date={"=(vMax_import_date)"}, days_late={">0 <31"}, brand={"Nike", "Puma"}>} account_id)

The variable vMax_import_date is max(import_date)

And below version gives me the correct count but the date is hard coded which it cannot be in the app.

Count(Distinct{<import_date={"2020-06-21"}, days_late={">0 <31"}, brand={"Nike", "Puma"}>} account_id)

[shanemichelon]

Try This:

Count(Distinct {$ <import_date={"=$(vMax_import_date)"}, days_late={">0 <31"}, brand={"Nike", "Puma"}>} account_id)

Set analysis needs to be enclosed in curly brackets.  Then the $ symbol is for the current selections, the angled brackets are the modifiers. To get variable values, enclose in dollar sign expansion.

[Xerxes]

Thanks.

With the suggested solution the date filter is not applied, the days_late and brand are applied so the result is the same as if I remove the date.

[shanemichelon]

Sorry, I just noticed your date variable is Max(import_date).  This give a numeric result.  You need to turn that back into a date with the Date function.  You can do it in the variable or in the set analysis:

Count(Distinct {$ <import_date={"=$(=Date(vMax_import_date))"}, days_late={">0 <31"}, brand={"Nike", "Puma"}>} account_id)

In QS expression editor you should be able to see the result of the set analysis at the bottom.

[NitinK7]

Hi,

try below expression

Count(Distinct ${<import_date={"$(=Date(Max(import_date),'YYYY-MM-DD'))"}, days_late={">0 <31"}, brand={"Nike", "Puma"}>} account_id)

[Xerxes]

This string gives 0 as result ->

Count(Distinct {$ <import_date={"=$(=Date(vMax_import_date))"}, days_late={">0 <31"}, brand={"Nike", "Puma"}>} account_id)

[Xerxes · Accepted solution]

Yes, close enough!

Moving the $ into the bracket worked.

Count(Distinct {$<import_date={"$(=Date(Max(import_date),'YYYY-MM-DD'))"}, days_late={">0 <31"}, brand={"Nike", "Puma"}>} account_id)