Hi,
I’m looking for creating a pivot table that is calculating for each month and equipment the total money spent.
My dataset has the columns “po_number”, “po_line_number”, “received_amount”, “month_received” and “equipment” (among others we don’t care now).
One po_number can have multiple po_line_number, and each po_line_number can be repeated multiple times, and for those cases, the received_amount, month_received and equipment will be repeated as well. So actually, the received_amount for a po_line_number, is the unique value that is repeated. Below one example:
po_number 
po_line_number 
received_amount 
month_received 
equipment 
456123 
1 
100 € 
May 
Refrigeration 
456123 
1 
100 € 
May 
Refrigeration 
456123 
2 
450 € 
June 
HVAC 
333123 
1 
900 € 
May 
Refrigeration 
775123 
1 
600 € 
August 
Conveyor 
From above example, actually the amount received in May for Refrigeration should be 1000€ (Only 100€ from 456123, and 900€ from 333123):

May 
June 
July 
August 
Refrigeration 
1000€ 



HVAC 

450€ 


Conveyor 



600€ 
How would I be able to achieve this, baring in mind that I don’t want to display on my pivot neither po_number nor the po_line_number?
Thanks for the support!
@galaugus ,
have a look at this post as level aware calculations are useful for different scenarios. ( LevelAware Calculations: Tips and Best Practices )
1 Like
Thanks for the quick reply. Exactly what I was looking for.
One further question. I have created my LAC calculated fields (3 in total) and based on a parameter, I’m choosing which one I want to be displayed. Everything is working apart from when wanting to sort my pivot based on this parameter, I’m receiving the following message:
“The Level Aware Calculation (LAC) aggregate expressions is not supported in this calculation at this moment. Please remove the LAC aggregation expression from metrics.”
Any idea what could be the bug?
Thanks!
Hi @galaugus,
Can you use LACW instead of LACA for this? If you want to use LACA in your table, you can keep your calculated fields as is but for sorting create another version of the calculated using LACW.
1 Like
Hey David, thanks for the suggestion. The thing is that I would need to use a LACW(LACA) nested function, since first need to calculate the money spent per po_line_number (using a LACA function; sum(max(received_amount, [{po_line_number}]), and from this calculation should use the LACW function, to calculate this number per category, correct? And as you said in your article (very well explained btw, thanks!) it is not possible.
Hi @galaugus,
I’m glad you found the article helpful.
If you want to sort, I think you’ll have to use LACW instead of LACA in your calculated field. LACW(LACA) isn’t allowed.
Try this calculated field:
sumOver(
received_mount / countOver(po_number, [po_number, po_line_number], PRE_AGG),
[equipment, month_received],
PRE_AGG
)
I’m basically counting how many times each instance of po_number appears in every combination of po_number and po_line_number, and dividing by that number to effectively eliminate the duplicates. It’s not as pretty as LACA but I think it should work.