topic Re: Set Modifiers with Advanced Searches in QlikView

Set Modifiers with Advanced Searches

turodionova — Wed, 11 Dec 2013 09:53:27 GMT

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!

Wed, 11 Dec 2013 10:22:19 GMT

Hi Tamara,

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

best regards

Chris

Wed, 11 Dec 2013 10:33:54 GMT

Try using = before avg:

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

turodionova — Wed, 11 Dec 2013 11:09:18 GMT

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 — Thu, 12 Dec 2013 10:24:24 GMT

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 — Fri, 13 Dec 2013 07:01:17 GMT

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.