topic Aggr with set Analysis in App Development

Aggr with set Analysis

johann_bauer — Mon, 23 Jan 2017 19:34:35 GMT

Hi folks,

looks like the problem with set analysis and aggr is not so uncommon.

I allready found some very interesting blogs from henric about this topic but wasnt able to solve this.

First of all i am using a drill down dimension but im not able to reference to this dimension in the expression for a visualization.

So I build a small workaround to get me the coloumn name of the current dimension and store this in a variable.

Now to my real problem.

I'am using this expression for my charts.

Sum({$<[Monat]={'$(vMtdYtdYeID)'},[Anlass]={'010-000-2016'},[Reporting]={'$(vKPI3)','$(vKPI2)','$(vKPI1)'}>}Betrag)

This expression works and gives me the sum over all mentioned restrictions. Now I want the min and max values grouped by my

drill down dimension. I read somewhere that the aggr function works like a group by in the load script.

First i tried following expression:

max(aggr(sum({$<[Monat]={'$(vMtdYtdYeID)'},[Anlass]={'010-000-2016'},[Reporting]={'$(vKPI1)','$(vKPI2)','$(vKPI3)'}>}Betrag),$(vOrganisationHierarchie)))

$(vOrganisationHierarchie) delivers the current column name of the drill down dimension

But the expression wont deliver any values:

My second try was to try the aggr function in the set analysis expression like this:

sum({$<[Monat]={'$(vMtdYtdYeID)'},[Anlass]={'010-000-2016'},[Reporting]={'$(vKPI1)','$(vKPI2)','$(vKPI3)'}>}Aggr(Betrag),$(vOrganisationHierarchie))

and the second try also didnt deliver any values.

tldr:

I need the min and max values of an set analysis grouped by a drill down dimension.

Hope somebody can help me with that.

Johann

Re: Aggr with set Analysis

sunny_talwar — Mon, 23 Jan 2017 19:58:00 GMT

How about this:

Max({$<[Monat]={'$(vMtdYtdYeID)'},[Anlass]={'010-000-2016'},[Reporting]={'$(vKPI1)','$(vKPI2)','$(vKPI3)'}>}aggr(sum({$<[Monat]={'$(vMtdYtdYeID)'},[Anlass]={'010-000-2016'},[Reporting]={'$(vKPI1)','$(vKPI2)','$(vKPI3)'}>}Betrag),$(vOrganisationHierarchie)))

or this

Re: Aggr with set Analysis

girirajsinh — Mon, 23 Jan 2017 21:17:22 GMT

Hi Johann

Try this,

max(aggr(sum({$<[Monat]={'$(vMtdYtdYeID)'},[Anlass]={'010-000-2016'},[Reporting]={'$(vKPI1)','$(vKPI2)','$(vKPI3)'}>}Betrag),$(=$(=vOrganisationHierarchie )) ))

Provided your variable vOrganisationHierarchie is defined as,

LET vOrganisationHierarchie='GetCurrentField(CyclicGroupDimension)';

Re: Aggr with set Analysis

johann_bauer — Tue, 24 Jan 2017 07:57:20 GMT

Hi Girirajsinh,

I tried to use your expression but it didnt worked. I also tried to use just one dimension from the drill down dimension zto ensure that the synatx is not the error.

By the way there is no GetCurrentField Function in qlik sense, but the variable delivers the same result.

Re: Aggr with set Analysis

johann_bauer — Tue, 24 Jan 2017 07:58:57 GMT

Hi Sunny,

your expressions also didnt worked for me. I know its kinda hard to write an expression without the qvf.

But i cant provide the qvf

KR Johann

Re: Aggr with set Analysis

qvqfqlik — Tue, 24 Jan 2017 08:01:06 GMT

Try selecting one of these in issue into your current selections. I am having same issue. I need help with this issue too.

Inconsistency with data selection

Re: Aggr with set Analysis

marcus_sommer — Tue, 24 Jan 2017 08:10:01 GMT

AFAIK there is no getcurrentfield() available in sense but there are workarounds for it:

QLIK SENSE: $(=GetCurrentField(Time))‌

Further I could imagine that this will be helpful to find a solution:

Q-Tip # 14 – AGGR() and Synthetic Dimensions | Natural Synergies

- Marcus

Re: Aggr with set Analysis

girirajsinh — Tue, 24 Jan 2017 09:36:09 GMT

Hi Johann

Then probably as others have suggested workaround for GetCurrentFiled()

You may try following

Same expression as I suggested above

and variable

vOrganisationHierarchie would be assuming you have CycliDimension like below Time Heirarchy

LET vOrganisationHierarchie= 'if(getselectedcount(Year)=0,'Year',

if(getselectedcount(Year)=1 and getselectedcount(Quarter)=0,'Quarter',

if(getselectedcount(Quarter)=1 and getselectedcount(MonthName)=0,'MonthYear',

if(getselectedcount(MonthName)=1 and getselectedcount(Date)=0,'Day',

if(getselectedcount(Date)=1, 'Day')))))'

Re: Aggr with set Analysis

johann_bauer — Tue, 24 Jan 2017 11:15:11 GMT

Hi Girirajsinh,

im currently using your workaround for the GetCurrentField in Sense.

Im will try the solutions posted above and give feedback