1 Reply Latest reply on Feb 14, 2019 10:49 PM by Esther Aller

    Lookup not working while calculating variance

    Amit Singla

      Hi All,

       

      I'm using two different data extracts to create my report. The first extract is for getting the actual week on week numbers. The second extract give me the week on week  budget/targets.

      I'm trying to calculate the week on week variance from actuals - budgets.

       

      The issues is, if there is no record for a particular group in the actuals for a particular week even when we had a budget, the variance is showing as blank. What I want to achieve is to have -ve of my budget value in these cases.

       

      I'm using the below calculated field to find the variance, kindly let me know how i can get the correct results.

       

      IFNULL(ZN(LOOKUP(SUM([Bundle Qty]),0)),0) - IFNULL(ZN(LOOKUP(sum([BUDGETS_FSR].[Bundle Qty]),0)),0)

       

      the above seems to be not working.

       

      Kindly help me what else could be done to get the desired result.

       

      Regards,

      Amit