Search code examples
spotfirespotfire-analyst

Calculate percentage difference in spotfire from previous values

I have a dataset with 2 types of vendors and each week contains different colors.

My requirement is to calculate the percentage difference of the particular color for the particular vendor from current week to the previous week.

for example: the percentage of color green for week 2, vendor A is 80%. Now I want to compare this with previous week's (week 1) green value of vendor A which is 40%. I want the output to say the percentage difference between the current 80% and previous 40%. The same applicable to Yellow color.

enter image description here

I am able to calculate the percentage of respective colors for each week for each vendor. I also created a new calculated column which gives the previous week number. But I don't know how to get the percentage of previous week and get the difference between previous week to the current week.

Any expert advices would be really helpful.

Solution

  • This should give you the percent from the previous week, same vendor and color:

    SN(Last([Percent]) OVER (Intersect([Vendor],[color],Previous([week]))),0)

    if there was no previous week etc, it is set to zero (SN function).

    After that it you need to calculate the percent difference with your method of choice.