Set date range as Set Modifier

[TimmyCNHi]

Hey community!

I've been struggling to define a data range as set modifier. This set analysis syntax works:

=Sum ({1<Attribute={F_2020_02,F_2020_03,F_2020_04,F_2020_05,F_2020_06,F_2020_07,F_2020_08,F_2020_09,F_2020_10,F_2020_11,F_2020_12,F_2021_01}>} Value)

It looks at an Excel with an Attribute column that holds month information: F_2020_1 is 1 January 2020 and so on. The goal is to have the date values adapting according to today's date so it can always look back 12 months.

I tried defining the attribute range in the loading script as such:

Let SavingMeterDateRange = 'F_'&Date(Today()-334,'YYYY_MM')&',F_'&Date(Today()-306,'YYYY_MM')&',F_'&Date(Today()-275,'YYYY_MM')&',F_'&Date(Today()-245,'YYYY_MM')&',F_'&Date(Today()-214,'YYYY_MM')&',F_'&Date(Today()-184,'YYYY_MM')&',F_'&Date(Today()-153,'YYYY_MM')&',F_'&Date(Today()-122,'YYYY_MM')&',F_'&Date(Today()-92,'YYYY_MM')&',F_'&Date(Today()-61,'YYYY_MM')&',F_'&Date(Today()-31,'YYYY_MM')&',F_'&Date(Today(),'YYYY_MM');

Which does correctly result in:

F_2020_02,F_2020_03,F_2020_04,F_2020_05,F_2020_06,F_2020_07,F_2020_08,F_2020_09,F_2020_10,F_2020_11,F_2020_12,F_2021_01

But when applying this to the syntax, the result is zero. I guess that's because it's not recognized the same way. How can I make Qlik Sense understand that it should look for the resulting attributes?

[Zhandos_Shotan]

It looks like you using Dual field. Check Num(Attribute) for numeric representation. And try to use numeric values in set analysis.

[TimmyCNHi]

The only place I'm using 'Dual' is in an unrelated graph to determine monthly progress. It's used like this:

=If(Attribute='F_2020_01',Dual('Jan 2020',1),
If(Attribute='F_2020_02',Dual('Feb 2020',2),
etc.

Are you saying this influences the use of 'Attribute' elsewhere?

How can Num help on a text value?

[Ksrinivasan]

hi, you can covert F_2020_01 into Num Date as below 

=Right(Replace([Default Date],'_','/'),7)

it will convert text to date and create new column 2020/01.

ksrinivasan

[TimmyCNHi]

Alright! I left this lingering for a while and got back to it. I now added a new column in my source data, 'AttributeDate', which simply converts the aforementioned 'Attribute' values to plain dates, formatted as DD/MM/YYYY. I finally reached my goal with the following set analysis:

=Sum ({$<AttributeDate={"$(='>' & Min(Date(today()-365,MM/YYYY)) & '<=' & Max(Date(today(),MM/YYYY)))"}>} Value)

If I'm not mistaken, this should encompass all data that occurs in the current month and the 11 previous months.

Thank you @Zhandos_Shotan and @Ksrinivasan for providing me with the much needed insights into reaching this outcome. Also thanks to @sunny_talwar for hinting the date range formatting here. I think I'll get the hang of QS some day 😁