IDT 4.1 Calculation in the Select Statement

[Former Member]

Hello,

I'm trying to replicate a calculation in the Universe that is currently being done at the report level. Ideally, this would be done in one measure in the Universe.  What I curently have at the report level is =Sum([Sales_GC_Net Value] Where ([Sales Order Type Id] InList ("KE";"TA";"ZEDI";"ZWEB"; "ZCN"; "L2")) - [Sales_GC_Net Value] Where ([Sales Order Type Id] InList ("RE"; "G2")))

This is a standard calculation at my company and I'd like to define it in the semantic layer.  Any thoughts?

Thanks,

Sam

[Former Member · Accepted solution]

Hi,

if your database supports the case stetement (or something else similar to if):

First Measure sums up the sales value when the order type is in the first list of values

sum(CASE WHEN Sales_Order_Type_ID IN ('KE','TA','ZEDI','ZWEB','ZCN','L2') THEN Sales_GC_Net_Value ELSE 0 END)

Second Measure sums up the sales value when the order type is in the second list of values.

sum(CASE WHEN Sales_Order_Type_ID IN ('RE','G2') THEN Sales_GC_Net_Value ELSE 0 END)

Third measure: First Measure - Second Measure, if you want to provide first and second measure as separate objects.

Or simply all in one go:

sum(CASE WHEN Sales_Order_Type_ID) IN (first list) THEN Sales_GC_Net_Value ELSE 0 END) - sum(CASE WHEN Sales_Order_Type_ID) IN (second list) THEN Sales_GC_Net_Value ELSE 0 END)

Martina

[Former Member · Accepted solution]

Hi Martina,

Thanks for your response.  The all in one case statement in IDT is what I was looking for.  It is working well. 

Thanks Again!

Sam

[amitrathi239]

Hi,

one option is to first create two measure objects.

measure 2:

Select part: Sum([Sales_GC_Net Value]

Where clause:[Sales Order Type Id] IN ('KE','TA','ZEDI','ZWEB', ',ZCN', 'L2')

measure 2:

Select part:sum(Sales_GC_Net Value)

Where clause:([Sales Order Type Id] IN ('RE', 'G2')

Final Object:

Select part: Sum(measure 1-measure 2)

Amit

[Former Member]

Thanks Amit.  How do I subtract the 2 measures in my final object.  If I use

sum(@Select(MEASURE1) - (@Select(MEASURE2) it does not incorporate the Where restricting conditions, just the select statements.

Sam

[amitrathi239]

Hi,

where clause is already mentioned in the  measure 1 and measure 2.

Can you try to see if this is working or not in the  webi?

Amit

[Former Member]

Hi Amit - This did work in Webi when I subtracted Measure 2 from Measure 1.  Thanks!