topic Set Analysis with multiple conditions in App Development

Set Analysis with multiple conditions

Xerxes — Mon, 20 Dec 2021 21:08:46 GMT

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)

Re: Set Analysis with multiple conditions

shanemichelon — Wed, 24 Jun 2020 06:21:51 GMT

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.

Re: Set Analysis with multiple conditions

Xerxes — Wed, 24 Jun 2020 06:50:41 GMT

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.

Re: Set Analysis with multiple conditions

shanemichelon — Wed, 24 Jun 2020 06:56:16 GMT

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.

Re: Set Analysis with multiple conditions

NitinK7 — Wed, 24 Jun 2020 08:27:42 GMT

Hi,

try below expression

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

Re: Set Analysis with multiple conditions

Xerxes — Wed, 24 Jun 2020 10:54:24 GMT

This string gives 0 as result ->

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

Re: Set Analysis with multiple conditions

Xerxes — Wed, 24 Jun 2020 10:57:35 GMT

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)