Aggr with set Analysis

[johann_bauer]

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.

KR

Johann

[sunny_talwar]

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

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

[girirajsinh]

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)';

[johann_bauer]

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.

[johann_bauer]

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

[qvqfqlik]

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

[marcus_sommer]

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

[girirajsinh]

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')))))'

[johann_bauer]

Hi Girirajsinh,

im currently using your workaround for the GetCurrentField in Sense.

Im will try the solutions posted above and give feedback