topic Re: Set Analysis with embeded functions in App Development

Set Analysis with embeded functions

Mon, 17 Oct 2016 01:54:54 GMT

Hey Guys,
I'm new to Qlik Sense and have a question regarding Set Analysis using some embedded function.

I use some embedded functions to get the latest Calendar Year, from my Filter Pane as so:

SubField(GetFieldSelections([Calendar_Year_Nbr],',',GetSelectedCount([Calendar_Year_Nbr])),',',GetSelectedCount([Calendar_Year_Nbr]))

When I embed this formula in a table it returns the correct result (Single Year of: 2016) based on my selection criteria.

As in the screenshot below:

However when I embed the formula as part of a Set Analysis in a KPI it still returns results from multiple years (2015,2016)
Here is my Set Analysis formula:
count( distinct {$< Calendar_Year_Nbr = {"=$(= SubField(GetFieldSelections([Calendar_Year_Nbr],',',GetSelectedCount([Calendar_Year_Nbr])),',',GetSelectedCount([Calendar_Year_Nbr])))"}>} Dim_Case_DEX_Key)

Here is the a screenshot of the KPIs with the calculated KPI from above. (highlighted in yellow):
As you can see it still contains the aggregated result of 2015,2016 rather then just 2016.

Its should give the same result as the KPI "Hardcoded 2016" on the left 1.49M
Any help would be much appreciated.

Regards Anto

Re: Set Analysis with embeded functions

sunny_talwar — Mon, 17 Oct 2016 15:56:36 GMT

May be it is still a text field. Try this:

Count(DISTINCT {$< Calendar_Year_Nbr = {"=$(=Num#(SubField(GetFieldSelections([Calendar_Year_Nbr], ',', GetSelectedCount([Calendar_Year_Nbr])), ',', GetSelectedCount([Calendar_Year_Nbr]))))"}>} Dim_Case_DEX_Key)

Re: Set Analysis with embeded functions

Mon, 17 Oct 2016 22:24:33 GMT

Hey Sunny,

Thanks for this but it didn't seem to make a difference still gives me the aggregated results, if more than one year is selected.

Regards Anto

Re: Set Analysis with embeded functions

sunny_talwar — Tue, 18 Oct 2016 02:51:27 GMT

Would it be possible for you to share a sample to see the issue?

Re: Set Analysis with embeded functions

Tue, 18 Oct 2016 03:41:49 GMT

You won't have access to my Enterprise data source and file is saved

on Qlik Sense Enterprise server.

Do you want me to replicate this solution on Qlik desktop,

with just some dummy data ?

Regards Anto

Re: Set Analysis with embeded functions

jonathandienst — Tue, 18 Oct 2016 05:02:40 GMT

What's wrong with using Max(Calendar_Year_Nbr) to get a single year value?

Re: Set Analysis with embeded functions

Tue, 18 Oct 2016 05:30:28 GMT

Hey Jonathan,
Tried that also gives me an aggregated result , also don't think it takes

into account the dynamic values I've selected

Re: Set Analysis with embeded functions

sunny_talwar — Tue, 18 Oct 2016 09:39:50 GMT

Dummy data sample would be enough if what jontydkpi‌ has provided doesn't work for you.

Best,

Sunny