2 Replies Latest reply on Jun 1, 2019 1:38 AM by Vipulbhatia29

    Need help to calculate the column formula:

    3521884

      Hello Experts,

       

      I am working on a column formula which is mentioned below :

      CASE

      WHEN

      "Fact-Shanpshots"."DOC_TYPE" NOT IN ('AK', 'AL', 'MD', 'ZA') AND 

      "Fact-Shanpshots"."DOC_TYPE" NOT LIKE 'Z%' and

      "Dim - Time - Balance Date"."DAY_ID" AND

      "Fact-Shanpshots"."BALANCE_INDICATOR" <> 'p'

      THEN

      "Fact-Shanpshots"."AMOUNT"

      else 0

      end

       

      I have to use the aggregation SUM and Distinct but don't know how to use this logic in rpd:

      something like:

      SUM(DISTINCT(CASE

      WHEN

      "Fact-Shanpshots"."DOC_TYPE" NOT IN ('AK', 'AL', 'MD', 'ZA') AND 

      "Fact-Shanpshots"."DOC_TYPE" NOT LIKE 'Z%' and

      "Dim - Time - Balance Date"."DAY_ID" AND

      "Fact-Shanpshots"."BALANCE_INDICATOR" <> 'p'

      THEN

      "Fact-Shanpshots"."AMOUNT"

      else 0

      end ))

       

      Any idea will be welcome.

       

      Thanks & Regards,

      Abhi

        • 1. Re: Need help to calculate the column formula:
          M De Fanti

          Hi,

          I do not understand the reason of using the distinct.

          I suppose that you have to sum "Fact-Shanpshots"."AMOUNT" if your coditions are verified.

           

          You can achieve this, for example, directly in the formula of you analysis with something like

                    filter("Fact-Shanpshots"."AMOUNT" using

                              "Fact-Shanpshots"."DOC_TYPE" NOT IN ('AK', 'AL', 'MD', 'ZA') AND 

                              "Fact-Shanpshots"."DOC_TYPE" NOT LIKE 'Z%' ..... )

                    where "Fact-Shanpshots"."AMOUNT" is a mesured aggregated by sum operator

           

          Regards

          Massimo

          • 2. Re: Need help to calculate the column formula:
            Vipulbhatia29

            Hi

             

            Think about it you are doing a distinct on measure column.

             

            You have data like

             

            India 100

            Usa 100

            China 200

            Australia 200

             

            Then you are just doing a distinct on measure which doesn't make sense you are treating measure as a dim attr which is something not unheard of but then you are trying to measure it again.

             

            Filter option as mentioned above will work to get you a filtered measure.