Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
I have a table with data looks like below. I want to display in a separate pivot table how many are meeting the targets per agent per region.
Region | Agent | Year | Month | SalesAmount |
X | A | 2020 | 4 | 200 |
X | A | 2020 | 5 | 50000 |
X | B | 2020 | 5 | 100000 |
Y | A | 2020 | 4 | 30000 |
Y | B | 2020 | 4 | 20000 |
Z | A | 2020 | 5 | 200000 |
Z | B | 2020 | 5 | 300000 |
Z | B | 2020 | 4 | 500 |
A selection input box is made available to select the target. 50000, 100000, 200000
Using Pivot Table I want to display how may are meeting the sales target based on the selected variable value.
Selected Value : 50000
Condition: Total Sales should be >= 50000 to meet the target:
Region | AgentCount |
X | 2 |
Y | 0 |
Z | 2 |
Total | 4 |
Select Value : 100000
Condition : Total Sales shoud be >= 100000
Region | AgentCount |
X | 1 |
Y | 0 |
Z | 2 |
Total | 3 |
I tried Sum and Count function combinations in the expression but to no avail.
Any help is highly appreciated.
BR, jess
What you need is some AGGR(). Code may need a little tweaking since I'm typing it in directly here and not into a Qlik engine...
Count(AGGR(Sum(SalesAmount),Region,Agent, Year, Month)>$(vSalesTarget))
Hi,
Thanks for the help.
I tweaked it, and below is the one working to meet my requirements.
SUM(FABS(Aggr(DISTINCT SUM(SalesAmount), Region, Agent) > $(vSalesTarget)))
BR, jess
What you need is some AGGR(). Code may need a little tweaking since I'm typing it in directly here and not into a Qlik engine...
Count(AGGR(Sum(SalesAmount),Region,Agent, Year, Month)>$(vSalesTarget))
Hi,
Thanks for the help.
I tweaked it, and below is the one working to meet my requirements.
SUM(FABS(Aggr(DISTINCT SUM(SalesAmount), Region, Agent) > $(vSalesTarget)))
BR, jess