Framework Manager and Creating Counts Based on Values

[gosoccer]

Hi Everyone.  :)
I have to use a sentence like the following to calculate different counts associated to different conditions in my Framework Model:

case
when ( [XXXXX].[CAR_TYPE_CD]  = '1')
then (TOTAL_BENZ_COUNT = 1)
when ( [XXXXX].[CAR_TYPE_CD]  = '2')
then (TOTAL_JAGS_COUNT = 1)
else (TOTAL_ALL_CARS_COUNT=1)
end

I'm trying to get the following to create total counts for Benz and for all cars.

I keep getting errors as a part of my FM MODEL Calculation Definition.

Thanks in advance for your time.

[MFGF]

Hi Everyone.  :)
I have to use a sentence like the following to calculate different counts associated to different conditions in my Framework Model:

case
when ( [XXXXX].[CAR_TYPE_CD]  = '1')
then (TOTAL_BENZ_COUNT = 1)
when ( [XXXXX].[CAR_TYPE_CD]  = '2')
then (TOTAL_JAGS_COUNT = 1)
else (TOTAL_ALL_CARS_COUNT=1)
end

I'm trying to get the following to create total counts for Benz and for all cars.

I keep getting errors as a part of my FM MODEL Calculation Definition.

Thanks in advance for your time.

Hi,

A calculation like this returns a result for itself (ie just this calculation), and you can't assign values to other items are you are attempting to do here.

If I'm understanding correctly, you need three calculations here. The first one (called BENZ_COUNT) would be:

if ([XXXXX].[CAR_TYPE_CD]  = '1') then (1) else (0)

The second (called JAGS_COUNT) would be:

if ([XXXXX].[CAR_TYPE_CD]  = '2') then (1) else (0)

The third (called ALL_CARS_COUNT) would be:

if ([XXXXX].[CAR_TYPE_CD]  not in ('1', '2')) then (1) else (0)

You would therefore end up with three items.

MF.

[gosoccer]

Yes, I actually end up doing that.

I opened a Question as a part of Report Author group to see how I can dropthe value in a 'Crosstab Space (with fact cells)'.
I'm trying to have different 'Crosstab Space (with fact cells)' so I can initiate different Drill-Thrus to different Reports.

Thx so much for your time. :) 8)

[cognostechie]

Hi -

If you do this in the Report then it would probably be ok but if you have a self-service BI model coming out of Framework Manager then this
approach will not work because you are basically aggregating the counts which would provide wrong results in the rollup, especially at the
Grand Total level. Counts are semi-additive measures and should not be aggregated. They should be calculated for each scenario instead of
aggregating the counts of the lower levels to come up with the count of the higher level. Try making a report with your calculation and try to
put different dimensions in the rows and check the grand total of the counts. I usually use 'count (distinct case. () )' and inside a calculated member
and set the aggregate property to calculated.

[gosoccer]

I greatly appreciate your suggestion. I'll see what I can do.  :)

[gosoccer]

yes, the counts are showing much nicer now. Thx :) :)