Trouble using lag function

Question & Answer
,
1

Hello,
i have a table with 4 columns, Product, Vendor, Cost, and Cost ranking (not sure if this is needed)
cost ranking is calculated by Product partition, so for each product we get the best vendor according to cost ranked. I want to include a column with the cost difference of all the other vendors from the best one.
was trying to do this with lag but i can a) only create a column with the costs shifted down one line b) i cannot subtract the value of that column with the original cost column. (aggregated something error)
even if this worked it would give me the cost difference of each consecutive vendor (1st from 2nd, 2nd from third etc)

is there a way to do this for all vendors vs the first vendor?

thanks

2

@Nikos_Panayotou I tried to make this work with Table functions but I wasn’t successful.

The way I would typically address this is by calculating the value of the top vendor in my data before it hits QuickSight. I would add a column say ‘TopVendorCostByProduct’ which would hold the Cost amount for the Top Vendor for a given Product. Then in QuickSight it’s easy to create a Calculated field with a simple formula ‘Cost - TopVendorTopVendorCostByProduct’.