Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
It looks like you using Dual field. Check Num(Attribute) for numeric representation. And try to use numeric values in set analysis.
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?
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
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 😁