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. ( Level-Aware 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 LAC-W instead of LAC-A for this? If you want to use LAC-A in your table, you can keep your calculated fields as is but for sorting create another version of the calculated using LAC-W.
1 Like
Hey David, thanks for the suggestion. The thing is that I would need to use a LAC-W(LAC-A) nested function, since first need to calculate the money spent per po_line_number (using a LAC-A function; sum(max(received_amount, [{po_line_number}]), and from this calculation should use the LAC-W 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 LAC-W instead of LAC-A in your calculated field. LAC-W(LAC-A) 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 LAC-A but I think it should work.