topic Re: set analysis issue in expression in QlikView

set analysis issue in expression

Edge — Sat, 16 Nov 2024 01:45:08 GMT

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)

Re: set analysis issue in expression

Arthur_Fong — Mon, 09 Dec 2019 01:18:01 GMT

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

Re: set analysis issue in expression

Edge — Tue, 10 Dec 2019 06:08:28 GMT

Many Thanks Arthur for your message.

I tried the below but it is not working.

Re: set analysis issue in expression

Edge — Tue, 10 Dec 2019 06:56:42 GMT

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)"}

Re: set analysis issue in expression

Edge — Tue, 10 Dec 2019 06:58:15 GMT

Many Thanks Arthur,

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