Need help to calculate the column formula:

User_B7ZTH

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

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

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.