topic Re: Set Analysis and Dual Function in App Development

Set Analysis and Dual Function

brunov86 — Sat, 16 Nov 2024 03:17:15 GMT

Hello,

I run into the same problem for days now and cannot find a solution.

I have 1 KPI which is defined by the following set analysis:

count(distinct {<[Master_LocalFiscalYear]={$(=max([Master_LocalFiscalYear]))},[ClassStatus]={'Registered'},
[FinalStatus]={'Absent','Failed','In-Progress','Passed'}>} [studentid])

This set analysis works wonder when a use my standard calendar year.

In this example it is a special year following the format YYYY/YYYY (LocalFiscal_Year).

As I cannot use a text value (LocalFiscal_Year), I have used a dual function to made all the calculations run on a single year value (LocalFiscal_YearSingle).

Script extract

...

MasterCalendarDateGrantedRC:

LOAD
*,

Calendar_Date as CalendarDateGrantedRC,
Calendar_Year as Master_CalendarYear,
Dual(LocalFiscal_Year,LocalFiscal_YearSingle) as Master_LocalFiscalYear

FROM [$(PEDirectory)Master_Calendar.csv]
(txt, utf8, embedded labels, delimiter is ',');

....

I think the issue is somehow with my dual function but I don't see why.

Any help would be really appreciated.

Thank you,

Bruno

Re: Set Analysis and Dual Function

AndrewHughes — Wed, 12 Feb 2020 19:45:39 GMT

I'm not entirely sure what you are trying to do here. But the dual function stores the value as a text, number pair. Dual(text,number). If your intention is to assign the string year values YYYY/YYYY to corresponding year value YYYY (i.e. 2019/2020 to 2020) then I would simply use the DATE#() function to convert the text to date.
https://help.qlik.com/en-US/sense/November2019/Subsystems/Hub/Content/Sense_Hub/Scripting/InterpretationFunctions/date_hash.htm

Re: Set Analysis and Dual Function

Vegar — Wed, 12 Feb 2020 22:20:07 GMT

When creating set modifiers with fixed values Qlik Sense will always compare your value with the text part of an dual. Therefore you need to create the text part of your field in the modifier expression, try using maxstring() instead of max() as max() returns the numeric value not the string value.

count(distinct {<[Master_LocalFiscalYear]={'$(=maxstring([Master_LocalFiscalYear]))'},[ClassStatus]={'Registered'},
[FinalStatus]={'Absent','Failed','In-Progress','Passed'}>} [studentid])

Re: Set Analysis and Dual Function

brunov86 — Wed, 12 Feb 2020 22:15:24 GMT

Hey Vegar,

Thank you for your suggestion unfortunately it doesn't work.

Re: Set Analysis and Dual Function

Vegar — Wed, 12 Feb 2020 22:21:01 GMT

I did a minor adjustment to my previous suggestion. Look for the red ' '.

Re: Set Analysis and Dual Function

brunov86 — Thu, 13 Feb 2020 14:09:44 GMT

Hello Vegar,

It works wounder ! Thank you very much 😊

I have the same set analysis but for the previous year so it means my expression is like that

count(distinct {<[Master_LocalFiscalYear]={$(=maxstring([Master_LocalFiscalYear])-1)},[ClassStatus]={'Registered'},
[FinalStatus]={'Absent','Failed','In-Progress','Passed'}>} [studentid])

maxstring is not working in that case because i cannot remove 1 to a string. Is it possible to do some substring in the set analysis to get the previous year (YYYY-1/YYYY-1)?

Re: Set Analysis and Dual Function

sunny_talwar — Thu, 13 Feb 2020 14:29:10 GMT

May be this

Count(DISTINCT {<[Master_LocalFiscalYear] = {$("=(SubField(MaxString([Master_LocalFiscalYear]), '/', 1)-1) & '/' & (SubField(MaxString([Master_LocalFiscalYear]), '/', 2)-1)"}, [ClassStatus] = {'Registered'}, [FinalStatus] = {'Absent', 'Failed', 'In-Progress', 'Passed'}>} [studentid])

Re: Set Analysis and Dual Function

brunov86 — Thu, 13 Feb 2020 21:15:49 GMT

Hi SUnny,

Thank you for your answer. Unfortunately it does not work None of my previous year set analysis work with Local Fiscal Year. It only works with Calendar Year.

Re: Set Analysis and Dual Function

Vegar — Fri, 14 Feb 2020 10:34:56 GMT

Try this expression.

count({<[Fiscal Year]= {"$(=max([Fiscal Year]-1)&'/'& max([Fiscal Year]))"} >} Date)

You will need to construct the string value inside your modifier in some way, eg. '2020/2021'. I my example above I make use of the numeric value of my dual to construct the string.

It works in my attached example.

Re: Set Analysis and Dual Function

brunov86 — Tue, 18 Feb 2020 13:58:21 GMT

Thank you very much Vegar.

It works really well !

Re: Set Analysis and Dual Function

Vegar — Tue, 18 Feb 2020 16:31:38 GMT

😃 glad I could help.