Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all
my datatable looks like this
SKU | PrimarySKU | Availability | Volume |
---|---|---|---|
A | A | 5 | 30 |
B | A | 7 | 20 |
C | A | 9 | 100 |
D | D | 3 | 10 |
E | E | 1 | 5 |
I am trying to create a pivot chart showing
SKU | Availability | Volume | PrimarySKU | Avail. PrimarySKU | VolumeOccupied | Exp VolOccupied |
---|---|---|---|---|---|---|
A | 5 | 30 | A | 5 | 30*5 | 30*5 |
B | 7 | 20 | A | 5 | 20*7 | 20*5 |
C | 9 | 100 | A | 5 | 100*9 | 100*5 |
D | 3 | 10 | D | 3 | 10*3 | 10*3 |
E | 1 | 5 | E | 1 | 5*1 | 5*1 |
Basically the SKU field is leading and for some of these, there is a common primary SKU
I would like to have some expressions that are calculations of fields directly related to SKU field (like VolumeOccupied, easy)
I also would like to calculate based on values linked to PrimarySKU field, like Avail. PrimarySKU. This is more difficult for me.
Or even a mixture of the two, so values linked to SKU together with values linked to PrimarySKUm as in Exp VolOccupied.
Any idea how to realise last two expressions?
thank you!
Hi,
a solution in attached file with ApplyMap function
Kind regards
thank you for your input.
indeed this one is a workable solution.
But I wouldn't like to change the script; and since I need to apply this to many, many fields, I would like to have some sort of set analysis to easily use values from different fields.
Here is something that works with this small data set
For the 1st calc , i used the total <PrimarySKU> to evaluate the subset of recrods for each unique Primary SKU (3 for PrimarySKU=A etc...) and spit out the record where SKU=PrimarySKU. If there are multiple records, just add the 'distinct' keyword after the "only(" part:
only( total <PrimarySKU> if(SKU=PrimarySKU,Availability))