Set Analysis and Dual Function

[brunov86]

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

[AndrewHughes]

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/Interpreta...

[Vegar]

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

[brunov86]

Hey Vegar,

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

[Vegar]

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

[brunov86]

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

[sunny_talwar]

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

[brunov86]

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.

[Vegar · Accepted solution]

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.

[brunov86]

Thank you very much Vegar.

It works really well !