Categories
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
Answers
-
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
0 -
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.
0