Set Modifiers with Advanced Searches

[turodionova]

Hello!

I am trying to create an expression using aggregations in advanced searches.

As a result i need to get Sales of all Managers with avg Total Monthly sales >50000.

Sum({$<Manager={"avg(aggr(sum(TOTAL<YearMonth,Manager> [Sales]), YearMonth,Manager))>50000"}>}[Sales]

Please help to understand why my formula does not work.

Thanks! 

[#2]

Hi Tamara,

is there any possibility you can use dimensional limit on simply sum(Sales) or avg(Sales) ?

best regards

Chris

[#3 · Accepted solution]

Try using = before avg:

Sum({$<Manager={"=avg(aggr(sum(TOTAL<YearMonth,Manager> [Sales]), YearMonth,Manager))>50000"}>}[Sales])

[turodionova]

Hi, Chris!

1) I do not need in my final calculation Manager dimension, but share of

all managers with monthly total sales > 50000. Appreciate any other

solution.

2) with = still does not work

[turodionova]

Hi, Anton!

Thank you! Now it works!

Kind regards

Tamara Rodionova

Colgate-Palmolive Russia.

Senior Sales Analyst

Office phone +7495 937 11 00 (ext. 2-822)

[turodionova]

Hi, Anton!

Can you please advice how to modify this expression in order to get

monthly result.

With another words i need to sum sales of managers with avg. sales per

month >10 000 000 within every month,

while now my expression takes sales of managers with avg. sales per month

10 000 000 in total.