5 Replies Latest reply: Aug 2, 2012 4:38 AM by Stefan Wühl

# set analysis on aggr function?

I am using following expression for calculation on a chart

=Count({\$<[Last 3 Months]=,[Last 6 Months]=,[Last 12 Months]= >}distinct(if(Year=Year([MinDateVarSession]), AggR(Timestamp,User_Pin,[Session Start]))))

I am using set analysis, because I don't want data to be effeted when I select Last 3 Months,Last 6 Months or Last 12 Months.
But my expression shows me correct data if I don't do any selections, but as soon as I select any of  Last 3 Months,Last 6 Months or Last 12 Months
the data gets changed according to selection made.
I think the set analysis is not working because of 'aggr' function.
But how can I make set anaysis work on aggr function?

Any ideas??

• ###### Re: set analysis on aggr function?

Try

=Count(

{\$<[Last 3 Months]=,[Last 6 Months]=,[Last 12 Months]= >}

distinct(if(Year=Year([MinDateVarSession]),

Aggr(

only({<<[Last 3 Months]=,[Last 6 Months]=,[Last 12 Months]= >} Timestamp)

,User_Pin,[Session Start])

)))

• ###### Re: set analysis on aggr function?

I usually use aggr function around an expression, instead of inside an expression...

aggr(count(Value), Dimension) --> count of "Value", for each item of "Dimension"

count(aggr(Value, Dimension)) --> what do you expect ?

• ###### Re: set analysis on aggr function?

try

Try

=Count(

{1<[Last 3 Months]=,[Last 6 Months]=,[Last 12 Months]= >}

distinct(if(Year=Year([MinDateVarSession]),

Aggr(

only({<<[Last 3 Months]=,[Last 6 Months]=,[Last 12 Months]= >} Timestamp)

,User_Pin,[Session Start])

)))

• ###### Re: set analysis on aggr function?

Hi swuehl,

Thst one was correct..it worked!!!Thanks a lot!!!!

But it would be really helpful if you could you tell me what this 'only' function is about, so

that I can use it in future.

Could not find much about it in help document.

• ###### Re: set analysis on aggr function?

Not sure if I can explain that better than the Help.

only(expression)

If expression evaluated to an unambiguous value in your context, this value will be returned, if it can only evaluate to ambiguous values, NULL is returned. Not so much different than just using expression.

But only() has one big advantage, it is an aggregation function. So you can use a set expression with only() function, and that's what needed here to disregard the selections in fields within the aggr() function.

Hope this helps,

Stefan