Problem with calculation / presentation of data

[#1]

Hi Everyone,

Please consider the following tables:

As you can see the "Round Amount" column differs from one another. I can understand why this would happen (because the conditional statement I use to evaluate the calculation in the top table is being performed seperately for each zone (which is EXACTLY what i want, but with the bottom table it is being performed as a sum of all the records.

How it works:

• A picker at our company picks goods to be delivered. Because some zones are more accessible than others, differt rates apply for each zone.
• "Picks" is the amount of picks he has done. "Sum/Tar" is the amount of picks the picker should be able to do within the time he has been picking in the zone.
• Now - for every pick over the target rate, we give him a 25c incentive. Thus the "Round Amount" column has the formula sum([PICKS]) - sum([SUM/TAR]) * 0.25

If you use this formula & apply it the tables above you will find that they are both correct. The problem is that I'm trying to create a table/pivot chart where my pickers will be one dimension & the other dimension will be the date picked. Such as:

The reason I want it to display like this is that now I can easily see how much money is being paid daily, as well as how much to pay a picker over a selected period of time.

ANY suggestions would be much appreciated!!

Regards,

S.H.du Toit

[Oleg_Troyansky · Accepted solution]

If I understand you correctly,your problem is caused by rounding (and I still don't understand why the "Round Amount" is 0 in Zone 2L)...

In any case, you need to perform your calculation at a more detailed level and then present the summary of pre-calculated results in your Pivot Table. You can do it using Advanced Aggregation (function AGGR() ) - something like this:

sum ( AGGR( round( sum([PICKS]) - sum([SUM/TAR]) * 0.25, 0.01), Picker, Date, Zone))

In this example, I added rounding to a penny, and pre-aggregated the results for Picker, Date and Zone. You can add more Dimensions if needed, just don't make it way too detailed. Once pre-aggregated and rounded at the lower level, the results can be then summarized to the higher levels (like Picker/Day and Picker)

cheers,

[#3]

I may be misunderstanding, or oversimplifying, but if you want your results displayed in a pivot table aggregated by day, why not create a pivot table, using your 'day' field as a dimension. You can then drag the day field to the top of the chart to have it display in columns, as youve indicated in your example.

[#4]

Hi,

First of all thanks for the responses. Oleg's solution was the one I was looking for - thank you very much I appreciate it!! Seems I had abit of a warped understanding of the advanced aggregation function where I thought it could not be used accross multipledimensions, but your solution cleared that right up.

Thank you for your response as well Fry. I did know how to create the pivot table, but because of my calculation not being correct my totals on the pivot table would not add up when tested in excel.

For other people who might find use in this solution & to clarify Oleg's question. The reason why 2L & 2H has a round amount of R0.00 is because I forgot to mention that if [PICKS] - 20 < [SUM/TAR] the incentive equals 0.

Thanks again for your inputs!!