Derived Measure (fact)

[Anonymous]

I have the following in my load Script:

RangeSum(MedicalAidAmount, Shortfall) as Amount,

Is it possible to have a derived measure (fact) based on Amount, Like

0.2*Amount as OtherAmount

Regards.

Chris

[sunny_talwar · Accepted solution]

Sure, try this:

LOAD RangeSum(MedicalAidAmount, Shortfall) as Amount,

          RangeSum(MedicalAidAmount, Shortfall) * 0.2 as OtherAmount

FROM ...

or

LOAD Amount * 0.2 as OtherAmount;

LOAD RangeSum(MedicalAidAmount, Shortfall) as Amount

FROM ...

[Anonymous]

Thanks Sunny, is it possible to assign such a derived fact to a named dimension say Company1 so that it is associated with this value?

Regards.

[sunny_talwar]

You mean that OtherAmount should have 0.2*Amount for Company1 and all other companies should have 0s for OtherAmount field?

[swuehl]

Maybe like

LOAD RangeSum(MedicalAidAmount, Shortfall) * If(NamedDimension = 'Company1', 0.2,1) as Amount,

          NamedDimension,

          ...

[Anonymous]

Thanks very much, i just want the new values to be associated with a named dimension.  The original values are already assigned to different companies and branches as follows:

Company Branch QTY Amount

River          SSS     45     56

Brigde        XXX     50     400

Sand          YYY     400     500

Manfred     Fred      OtherQTY     OtherAmount

I now need OtherAmount and OtherQTY to be assigned to a DerivedDimension say Manfred and Fred as in bold above

[sunny_talwar]

How is OtherQTY and OtherAmount getting calculated here? From the data for River, Bridge and Sand? I am not really sure I understand what you are trying to do here.

[Anonymous]

Yes it is being calculated fro the River, Bridge and Sand Data.  The OtherQTY and OtherAmount represents organisations which have not been sample and is an estimate for these organizations as a whole.

[sunny_talwar]

May be something like this:

Table:

LOAD Company,

          Branch,

          QTY,

          Amount

FROM Source;

Concatenate (Table)

LOAD 'Manfred' as Company,

          'Fred' as Branch,

          Avg(QTY) * 0.2 as QTY,

          Avg(Amount) * 0.2 as Amount

Resident Table;

For you sample, you will get a table like this:

Company Branch QTY Amount

River          SSS    45    56

Brigde        XXX    50    400

Sand          YYY    400    500

Manfred    Fred    39.67  63.73

Where QTY = Avg(45, 50, 400) * 0.2          and

          Amount = Avg(56, 400, 500) * 0.2

Is this what you are looking for?

[Anonymous]

Hi Sunny, that should do, let me apply it and let you know.

Regards.