Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
brunov86
Contributor II
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:

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

Labels (4)
1 Solution

Accepted Solutions
Vegar
MVP
MVP

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.

image.png

View solution in original post

10 Replies
AndrewHughes
Partner - Contributor III
Partner - Contributor III

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
MVP
MVP

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
Contributor II
Contributor II
Author

Hey Vegar,

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

 

Vegar
MVP
MVP

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

brunov86
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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
MVP
MVP

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.

image.png

brunov86
Contributor II
Contributor II
Author

Thank you very much Vegar.

It works really well !