1 Reply Latest reply on Dec 4, 2018 7:47 PM by swaroop.gantela

    Null Value

    Welly Pratama
    Welly Pratama Dec 4, 2018 7:03 PM

      Hi guys, i have a problem here :

      i want to show the value of lastest date in the week, but i have the data likes that. in 14 October the user didn't input the value and i want to make some calculation to replace it with the value from 13 october. I have try to make a calculation to prevent null value :

       

      if ISNULL([Retail Price])

      THEN date(DATETRUNC('week',DATEADD('week',1,[Sales Date]),'Monday')-2)

      ELSE date(DATETRUNC('week',DATEADD('week',1,[Sales Date]),'Monday')-1)

      END

       

      but i cannot use the calculation because there's an error "Cannot mix aggregate and non-aggregate comparisons or results in 'IF' expressions"

       

      anyone can help me with this problem?

      • 36 Views
      • Categories: Calculations, Data Sources & Connections
      • Tags:
        • 1. Re: Null Value
          swaroop.gantela
          swaroop.gantela Dec 4, 2018 7:47 PM (in response to Welly Pratama)

          Welly,

           

          I'm not sure if I recreated your setup, but maybe this can give an idea.

           

          You could try a calculated field like:

          IFNULL( SUM( [Value] ), LOOKUP ( SUM ( [Value] ), -1 ) )

           

          In your calculated field, is your [Retail Price] an aggregate

          and [Sales Date] is not? If so, you should be able to aggregate

          [Sales Date] with an ATTR or maybe a MAX or MIN.

           

          Please see workbook v10.3 attached in the Forum Thread:

          Null Value

           

          289448null.png

          1 of 1 people found this helpful