Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Edge
New Contributor III

set analysis issue in expression

Hello all,

 

I have the below set analysis for a chart. I have another field  "semester and year name" in the dimensions. I want the below formula to distinct count "EMPLID " for semesters whose codes = the value of the ones in the chart -100.

 

Could you please help? My main issue is with SemeserNO set analysis.

 

Count(distinct {$<[Semester Year Name]=,[Appliation Submit Date]={'<=$(=Date(Today()-365))'},[SemesterNO]={'=$(=SemesterNO-100)'}>} AppAdmEnr.EMPLID)

1 Solution

Accepted Solutions
Edge
New Contributor III

Re: set analysis issue in expression

Hey,

I just wanted to update on this:

So what kind of worked with me is the below, although it is not the final version.

max({$<[Semester Year Name]=,[SemesterNO]={"$(=max(SemesterNO)-100)","$(=max(SemesterNO,2)-100)"}>} SemesterNO)

To explain, in the set analysis, I disregard the [Semester Year Name] selection with this [Semester Year Name]=

But even though I disregarded the selection, I can get the previous semester by subtracting 100 from the selected semesters with this: [SemesterNO]={ "$(=max(SemesterNO)-100)"
Seems like QlikView somehow knows that it needs to subtract 100 from the semesters in the filter which are showing in the chart. Don't ask me how or why.

Finally, because I have 2 semesters in the chart I use max(somefield,2). The 2 in the max formula fetches me the second max. if there is only semester it will show that, with this:[SemesterNO]={"$(=max(SemesterNO)-100)","$(=max(SemesterNO,2)-100)"}

View solution in original post

4 Replies
Partner
Partner

Re: set analysis issue in expression

Try this:
Count(distinct {$<[Semester Year Name]=,[Appliation Submit Date]={'<=$(=Date(Today()-365))'},[SemesterNO]={"$(=max(SemesterNO)-100)"}>} AppAdmEnr.EMPLID)
Edge
New Contributor III

Re: set analysis issue in expression

Many Thanks Arthur for your message. 

I tried the below but it is not working.

Edge
New Contributor III

Re: set analysis issue in expression

Hey,

I just wanted to update on this:

So what kind of worked with me is the below, although it is not the final version.

max({$<[Semester Year Name]=,[SemesterNO]={"$(=max(SemesterNO)-100)","$(=max(SemesterNO,2)-100)"}>} SemesterNO)

To explain, in the set analysis, I disregard the [Semester Year Name] selection with this [Semester Year Name]=

But even though I disregarded the selection, I can get the previous semester by subtracting 100 from the selected semesters with this: [SemesterNO]={ "$(=max(SemesterNO)-100)"
Seems like QlikView somehow knows that it needs to subtract 100 from the semesters in the filter which are showing in the chart. Don't ask me how or why.

Finally, because I have 2 semesters in the chart I use max(somefield,2). The 2 in the max formula fetches me the second max. if there is only semester it will show that, with this:[SemesterNO]={"$(=max(SemesterNO)-100)","$(=max(SemesterNO,2)-100)"}

View solution in original post

Edge
New Contributor III

Re: set analysis issue in expression

Many Thanks Arthur,

Your suggestion has helped me make the expression that worked with me.