Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the following type data:
^DeptCust | Dept | Customer | CustomerPct | Year | Month | Commission |
A|1 | A | 1 | 40% | 2012 | Jan | 100 |
A|4 | A | 4 | 50% | 2012 | Jan | 150 |
A|3 | A | 3 | 55% | 2012 | Jan | 200 |
A|2 | A | 2 | 60% | 2012 | Jan | 250 |
A|5 | A | 5 | 100% | 2012 | Jan | 300 |
A|1 | A | 1 | 40% | 2012 | Feb | 400 |
A|2 | A | 2 | 60% | 2012 | Feb | 450 |
A|2 | A | 2 | 60% | 2012 | Feb | 500 |
A|3 | A | 3 | 65% | 2012 | Feb | 550 |
A|4 | A | 4 | 100% | 2012 | Feb | 600 |
I need an object where a year and month are selected and a variable for the CustomerPct. So (use data above) if a user selects Feb and 2012 and a CustomerPct of 65%, then the object returns all data from February that has a % of 65% or less. This expression works fine:
sum({<^DeptCust = {"=[CustomerPct]<=$(vRetensionPct)"} >}Commission)
and would return all blue lines from Feb 2012.
However I need it to take all months = or less than the month selected FOR the combinations that are valid for February (A|1,A|2, and A|3).
The result would look like:
Dept Commission Distinct Customer Count
A 2450 3
Looking for Commission to be YTD and how to get a distinct customer count.
Have Dept has dimesnion
Two expression:
1). Sum({<^DeptCust = {"=[CustomerPct]<=$(vRetensionPct)"} >}Commission)
2). Count({<^DeptCust = {"=[CustomerPct]<=$(vRetensionPct)"} >} DISTINCT Customer)
Sum({<^DeptCust = {"=[CustomerPct]<=$(vRetensionPct)"} >}Commission)
This formula doesn't work because if I select February it gives me ONLY February commission. I need February plus all previous months (ie YTD). So choosing Feb 2012 and the variable percent establishes what customer/dept combinations you want but then I need it to be all YTD commissions of that combination...essentially all the lines in blue in my data set and the object shows the result (above).
Thanks for helping!
Do you have date field?
Make use of it.
Sum({<^DeptCust = {"=[CustomerPct]<=$(vRetensionPct)"}, DateField={">=$(=YearStart(Max(DateField)))<=$(=Max(DateField))"}, Year=, Month= >}Commission)