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]

Hi!

Did you try this?:

=Sum ({1<Attribute={$(SavingMeterDateRange)}>} Value)

[TimmyCNHi]

Ah yes, forgot to mention that. That's the syntax that leads to zero.

[Ksrinivasan]

hi,

Try this,

Sum({<Year= ,Month=, [Calendar Year/Month] = {">=$(=AddMonths(Max([Calendar Year/Month])), -12)<=$(=Max([Calendar Year/Month])) "}>} Amount)

ksrinivasan

[TimmyCNHi]

Hi! Thank you for your input. It seems to work (when changing 'Amount' to 'Value') but results in a much higher value than expected. I think the issue is that I need QS to look for text and not actual date information. Perhaps I should change/convert this in my source data.

The attribute column holds the calendarization of my project. It looks a bit like this:

To take the past 12 months into account (including current one), it needs to limit the range to this set:

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

Next month it will be this set:

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,F2021_02

I figured if I simulated this set based on dates in the load script it would work but apparently it's not that straightforward.

[TimmyCNHi]

I also tried to put the SavingMeterDateRange definition in a Text() function as below.. Still a no go.

Let SavingMeterDateRange = Text('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'));

Looks good when just looking at SavingMeterDateRange output though:

[TimmyCNHi]

Another quick update.

Even if I define the following in the load script:

Let SavingMeterDateRange = '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';

This syntax still returns zero:

=Sum ({1<Attribute={SavingMeterDateRange}>} Value)

Only inserting the range into the syntax itself 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)

Why is this different for QS?

[TimmyCNHi]

Dividing it up doesn't work either. 

Script:

Let SavingMeterDate1 = 'F_2020_02';
Let SavingMeterDate2 = 'F_2020_03';
Let SavingMeterDate3 = 'F_2020_04';

Syntax:

=Sum ({$<Attribute={SavingMeterDate1,SavingMeterDate2,SavingMeterDate3}>} Value)

Still zero. 🙁

[Zhandos_Shotan]

Text values of Attribute field must be in quotes 'val1', 'val2',... 

=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)

So you need to check first:

Set SavingMeterDateRange = "'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'";

Then If it works on Set analysis, put values into qutes in variable.

Pls. ref to:

https://help.qlik.com/en-US/sense/November2020/Subsystems/Hub/Content/Sense_Hub/Scripting/use-quotes...

[TimmyCNHi]

Thank you very much for your efforts but it doesn't seem to work either. About the quotes: that doesn't seem to matter; works with or without them.

I'll try a different approach by manipulating source data on Monday. Have a great weekend!