Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, community!
I need to calculate indicator 'Client with Sales amount Growth':
'Client with Sales amount Growth' = (Client w Sales Amount - Client w Sales Amount YB) / Client w Sales Amount YB.
Problem is that 'Client w Sales Amount' and 'Client w Sales Amount YB' are in different periods.
Is it possible to solve such a problem?
Both .. 'Client w Sales Amount' and 'Client w Sales Amount YB' calculate on the fly.
Expressions are pretty massive because of Calculated periods(QRT, FY, YTD ...):
For 'Client w Sales Amount' :
IF($(vPeriodCalcSelected)='NoCalc',COUNT({$<ClientHasSalesInMnth = {"-1"}>} DISTINCT %CLIENT_ID),
IF($(vPeriodCalcSelected)='QRT',COUNT({$ <Start_Date = {">=$(=vLastMQuarterStartDate)<=$(=vLastMQuarterEndDate)"}, ClientHasSalesInMnth = {"-1"}>} DISTINCT %CLIENT_ID),
IF($(vPeriodCalcSelected)='YTD',COUNT({$ <Start_Date = {">=$(=vLastMYearStartDate)<=$(=vLastMonthStartDate)"},ClientHasSalesInMnth = {"-1"}>} DISTINCT %CLIENT_ID),
IF($(vPeriodCalcSelected)='FY',COUNT({$ <Start_Date = {">=$(=vLastMYearStartDate)<=$(=vLastMYearEndDate)"},ClientHasSalesInMnth = {"-1"}>} DISTINCT %CLIENT_ID),
IF($(vPeriodCalcSelected)='MQT',COUNT({$ <Start_Date = {">=$(=vLastMFloatQuarterStartDate)<=$(=vLastMonthStartDate)"}, ClientHasSalesInMnth = {"-1"}>} DISTINCT %CLIENT_ID),
IF($(vPeriodCalcSelected)='MAT',COUNT({$ <Start_Date = {">=$(=vLastMFloatYearStartDate)<=$(=vLastMonthStartDate)"}, ClientHasSalesInMnth = {"-1"}>} DISTINCT %CLIENT_ID),
IF($(vPeriodCalcSelected)='QTG',COUNT({$ <Start_Date = {">$(=vLastMonthStartDate)<=$(=vLastMQuarterEndDate)"}, ClientHasSalesInMnth = {"-1"}>} DISTINCT %CLIENT_ID),
IF($(vPeriodCalcSelected)='YTG',COUNT({$ <Start_Date = {">$(=vLastMonthStartDate)<=$(=vLastMYearEndDate)"}, ClientHasSalesInMnth = {"-1"}>} DISTINCT %CLIENT_ID),
'Custom'))))))))
'Client w Sales Amount YB':
IF($(vPeriodCalcSelected)='NoCalc',COUNT({$<Start_Date = {$(=Start_Date_YB)}, ClientHasSalesInMnth = {"-1"}>} DISTINCT %CLIENT_ID),
IF($(vPeriodCalcSelected)='QRT',COUNT({$ <Start_Date = {">=$(=vLastMQuarterStartDate_YB)<=$(=vLastMQuarterEndDate_YB)"}, ClientHasSalesInMnth = {"-1"}>} DISTINCT %CLIENT_ID),
IF($(vPeriodCalcSelected)='YTD',COUNT({$ <Start_Date = {">=$(=vLastMYearStartDate_YB)<=$(=vLastMonthStartDate_YB)"},ClientHasSalesInMnth = {"-1"}>} DISTINCT %CLIENT_ID),
IF($(vPeriodCalcSelected)='FY',COUNT({$ <Start_Date = {">=$(=vLastMYearStartDate_YB)<=$(=vLastMYearEndDate_YB)"},ClientHasSalesInMnth = {"-1"}>} DISTINCT %CLIENT_ID),
IF($(vPeriodCalcSelected)='MQT',COUNT({$ <Start_Date = {">=$(=vLastMFloatQuarterStartDate_YB)<=$(=vLastMonthStartDate_YB)"}, ClientHasSalesInMnth = {"-1"}>} DISTINCT %CLIENT_ID),
IF($(vPeriodCalcSelected)='MAT',COUNT({$ <Start_Date = {">=$(=vLastMFloatYearStartDate_YB)<=$(=vLastMonthStartDate_YB)"}, ClientHasSalesInMnth = {"-1"}>} DISTINCT %CLIENT_ID),
IF($(vPeriodCalcSelected)='QTG',COUNT({$ <Start_Date = {">$(=vLastMonthStartDate_YB)<=$(=vLastMQuarterEndDate_YB)"}, ClientHasSalesInMnth = {"-1"}>} DISTINCT %CLIENT_ID),
IF($(vPeriodCalcSelected)='YTG',COUNT({$ <Start_Date = {">$(=vLastMonthStartDate_YB)<=$(=vLastMYearEndDate_YB)"}, ClientHasSalesInMnth = {"-1"}>} DISTINCT %CLIENT_ID),
'Custom'))))))))