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

# Need help to calculate the column formula:

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:

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:

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.