Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
I have a question I cant seem to solve.
I have a following table in QS.
Parent Company | Potential | Actual | COL D :Share of Wallet (SoW) | COL E: Total Visits (TVs) | Bucket |
Customer A | 656,130 | 1,019,145 | 155% | 31 | 1 |
Customer B | 220,307 | 109,574 | 50% | 31 | 1 |
Customer C | 239,464 | 80 | 0% | 3 | 4 |
Customer D | 416,667 | 77,915 | 19% | 12 | 2 |
Customer E | 81,418 | 29,043 | 36% | 7 | 4 |
Customer F | 206,897 | -3,099 | -1% | 16 | 2 |
Customer G | 82,375 | 78,694 | 96% | 3 | 3 |
Customer H | 90,517 | 5,447 | 6% | 11 | 2 |
Customer I | 306,513 | 14,467 | 5% | 14 | 2 |
Customer J | 78,544 | 86,192 | 110% | 12 | 1 |
Customer K | 35,920 | 676 | 2% | 4 | 4 |
Customer L | 47,893 | 23,256 | 49% | 6 | 3 |
Customer M | 0 | 13,806 | - | 4 | 3 |
Customer N | 60,824 | 17,712 | 29% | 11 | 2 |
Customer O | 95,785 | 2,934 | 3% | 1 | 4 |
Customer P | 0 | 4,243 | - | 0 | 3 |
Customer Q | 0 | 875 | - | 0 | 3 |
Share of Wallet = Actual/Potential;
AVG_SoW (COL $E$19 in Below criteria) = 40% found by
>>>avg(aggr(Actual/Potential ,[Parent Company],[Sales Employee]));
AVG_TVs (COL $D$19 in Below criteria) = 9.76 found by
>>>> sum(aggr(count([Total Visits])/count([Parent Company]),[Parent Company],[Sales Employee]));
What i want to do is generate the Buckets column based on the following criteria:
Note: Col E is SoW and Col D is TVs in above table
=IF(AND(E2 >$E$19,D2>$D$19),"1",
IF(AND(E2>$E$19,D2<$D$19),"2",
IF(AND(E2<$E$19,D2>$D$19),"3", "4")))
My Probelm:
Im getting the following in QS
Parent Company | Potential | Actual | COL D :Share of Wallet (SoW) | COL E: Total Visits (TVs) | Bucket |
Customer A | 656,130 | 1,019,145 | 155% | 31 | 1 |
Customer B | 220,307 | 109,574 | 50% | 31 | - |
Customer C | 239,464 | 80 | 0% | 3 | - |
Customer D | 416,667 | 77,915 | 19% | 12 | 1 |
Customer E | 81,418 | 29,043 | 36% | 7 | - |
Customer F | 206,897 | -3,099 | -1% | 16 | - |
Customer G | 82,375 | 78,694 | 96% | 3 | - |
Customer H | 90,517 | 5,447 | 6% | 11 | - |
Customer I | 306,513 | 14,467 | 5% | 14 | - |
Customer J | 78,544 | 86,192 | 110% | 12 | - |
Customer K | 35,920 | 676 | 2% | 4 | - |
Customer L | 47,893 | 23,256 | 49% | 6 | - |
Customer M | 0 | 13,806 | - | 4 | - |
Customer N | 60,824 | 17,712 | 29% | 11 | - |
Customer O | 95,785 | 2,934 | 3% | 1 | - |
Customer P | 0 | 4,243 | - | 0 | - |
Customer Q | 0 | 875 | - | 0 | - |
Your help is greatly appreciated.
Please let me know if your need any clarification.
Thank you.
Hi Ruben,
Thank you for your reply. my QS script already had them in variables and i was already using the logic you mentioned above and getting those blanks in my post.
However, i was able to make it work by putting TOTAL in the AVG formulas so they dont change with dimensions when i use them in a table.
AVG_SoW = avg(TOTAL aggr(Actual/Potential ,[Parent Company],[Sales Employee]));
AVG_TVs = sum(aggr(count(TOTAL [Total Visits])/count(TOTAL [Parent Company]),[Parent Company],[Sales Employee]));
Thank you for your help.
Hi, you can use a variable to store $E$19, like creating a variable vAVG_SoW with value:
=avg(aggr(Actual/Potential ,[Parent Company],[Sales Employee]))
So this variable is calculated outside the table and dimensions won't affect it's result, same for D's expression, the you can use:
IF(avg(aggr(Actual/Potential ,[Parent Company],[Sales Employee]))>$(vAVG_SoW) and sum(aggr(count([Total Visits])/count([Parent Company]),[Parent Company],[Sales Employee]))>$(vAVG_TVs ), '1',
IF...
If buckets are fixed (not affected by selections) it will be better to have this calculated on script.
Hi Ruben,
Thank you for your reply. my QS script already had them in variables and i was already using the logic you mentioned above and getting those blanks in my post.
However, i was able to make it work by putting TOTAL in the AVG formulas so they dont change with dimensions when i use them in a table.
AVG_SoW = avg(TOTAL aggr(Actual/Potential ,[Parent Company],[Sales Employee]));
AVG_TVs = sum(aggr(count(TOTAL [Total Visits])/count(TOTAL [Parent Company]),[Parent Company],[Sales Employee]));
Thank you for your help.