I'm trying to create a customer feedback dashboard. I have a table with week, store name, fulfillment level data. I want to create a tableau viz which has the current value of KPI and 6 week ago value of the same KPI. These KPIs are calculated fields. Similarly, I need to get the previous week value of the KPIs. There are 10-15 KPIs.
I tried to implement the LOD function in a smaller dataset with just week number and one KPI. I referred to the following link https://community.tableau.com/s/question/0D54T00000CWdZqSAL/lead-and-lag-using-level-of-detail-calculations
The code which I tried is:
// There is no previous observation before the first observation.
IF [Order] <= { FIXED : MIN([Order]) } THEN
NULL
// Even ordered observation use the odd based windowed to look back.
// Note that the aggregate is superfluous, because we are nulling out
// the even ordered observation.
ELSEIF [Order] % 2 = 0 THEN
{ FIXED CEILING([Order] / 2) : min(IIF([Order] % 2 = 1,[NPS] , NULL)) }
// Odd ordered observation use the even based windowed to look back.
// Note that the aggregate is superfluous, because we are nulling out
// the odd ordered observation.
ELSE
{ FIXED FLOOR([Order] / 2) : MIN(IIF([Order] % 2 = 0, [NPS] , NULL)) }
END
But since NPS is a calculated field this is throwing and error: cannot mix aggregate and non-aggregate arguments in this function, which is the IIF().
Also, this particular code give just the previous row value, I need the same for 6th row prior to the current row. Also, in the main dataset there will be multiple rows for a given week number so how to I add the Order column in that case or is there any other better way to do this?
Self join in tableau is one option but it's not efficient, so I don't want to use it.