Pivot Table Column Subtraction Issue

[erric3210]

Hi Community,

I need to subtract column1 with column2, column3 with column2, etc.

Sample Data:

LOAD * INLINE [
Mode, Work_Date, Amount
Car, 12/29/2022, 1000
Car, 12/30/2022, 2000
Car, 12/31/2022, 3000
Truck, 12/29/2022, 4000
Truck, 12/30/2022, 5000
Truck, 12/31/2022, 6000
Bus, 12/29/2022, 7000
Bus, 12/30/2022, 8000
Bus, 12/31/2022, 9000
];

Desired Output:

Please help.

Regards,
Eric

[erric3210]

Has anyone ever faced this scenario.

[vinieme12]

I would suggest you use a straight table with each date /difference column being a different measure

this is not possible with a pivot table

[marcus_sommer]

Within the many scenarios the from @vinieme12 suggested way to create for each column an own measure is the most practically one.

Displaying it on a dimensional level is possible but you need to have these dimension-values within your data. This mustn't be within the origin field else it could be done in one or more extra fields respectively tables. Of course it will cost some extra efforts depending on the data-model, size of the data-set, the number of extra dimension-values and how dynamically it should be.