topic Re: Set date range as Set Modifier in App Development

Set date range as Set Modifier

TimmyCNHi — Sat, 16 Nov 2024 17:37:04 GMT

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?

Re: Set date range as Set Modifier

Zhandos_Shotan — Wed, 13 Jan 2021 17:12:32 GMT

Hi!

Did you try this?:

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

Re: Set date range as Set Modifier

TimmyCNHi — Thu, 14 Jan 2021 07:05:50 GMT

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

Re: Set date range as Set Modifier

Ksrinivasan — Thu, 14 Jan 2021 09:23:45 GMT

hi,

Try this,

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

ksrinivasan

Re: Set date range as Set Modifier

TimmyCNHi — Thu, 14 Jan 2021 11:02:50 GMT

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.

Re: Set date range as Set Modifier

TimmyCNHi — Thu, 14 Jan 2021 11:00:07 GMT

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:

Re: Set date range as Set Modifier

TimmyCNHi — Thu, 14 Jan 2021 15:20:00 GMT

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?

Re: Set date range as Set Modifier

TimmyCNHi — Thu, 14 Jan 2021 15:28:47 GMT

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. 🙁

Re: Set date range as Set Modifier

Zhandos_Shotan — Thu, 14 Jan 2021 15:37:13 GMT

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-in-script.htm

Re: Set date range as Set Modifier

TimmyCNHi — Fri, 15 Jan 2021 14:55:36 GMT

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!

Re: Set date range as Set Modifier

Zhandos_Shotan — Tue, 19 Jan 2021 05:03:20 GMT

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

Re: Set date range as Set Modifier

TimmyCNHi — Tue, 19 Jan 2021 09:49:33 GMT

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?

Re: Set date range as Set Modifier

Ksrinivasan — Tue, 19 Jan 2021 13:19:46 GMT

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

Re: Set date range as Set Modifier

TimmyCNHi — Thu, 04 Feb 2021 13:26:51 GMT

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 😁