Qlik Sense App Development

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
New Contributor II

Set Analysis and Dual Function

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:

*,

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

Labels (5)

• Set Analysis

1 Solution

Accepted Solutions
Highlighted
Partner

Re: Set Analysis and Dual Function

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.

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
10 Replies
Highlighted
Partner

Re: Set Analysis and Dual Function

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...
Highlighted
Partner

Re: Set Analysis and Dual Function

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
New Contributor II

Re: Set Analysis and Dual Function

Hey Vegar,

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

Highlighted
Partner

Re: Set Analysis and Dual Function

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
New Contributor II

Re: Set Analysis and Dual Function

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

Highlighted
MVP

Re: Set Analysis and Dual Function

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

Highlighted
New Contributor II

Re: Set Analysis and Dual Function

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.

Highlighted
Partner

Re: Set Analysis and Dual Function

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.

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
New Contributor II

Re: Set Analysis and Dual Function

Thank you very much Vegar.

It works really well !