11 Replies Latest reply: Dec 16, 2016 8:58 AM by bruno bertels

# Conditional Calculation

Here is the problem.

Good morning. I have a fact table containing daily and hourly electricity consumption. In Excel (pre load) I added a column DayNIght with values of "Day or Night" dependent upon the timestamp - so a flag. I want to multiply Consumption by the correct rate in a related dimension table dependent upon the value of the flag. The relationships are fine but I can't get the conditional logic to work either with 'If" or Set Analysis. Can someone help, please?

Denis

• ###### Re: Conditional Calculation

Hi Denis,

It is not possible to understand where is the problem unless you give us some example of the data along with calculation which you want.

Regards,

Kaushik Solanki

• ###### Re: Conditional Calculation

How can I best upload some rows from my tables? Will a screenshot be OK?

Many thanks,
Denis

• ###### Re: Conditional Calculation

You can create another Application with some sample data which can show us the issue which you are facing.

Regards,

Kaushik Solanki

• ###### Re: Conditional Calculation

Here is a sample app with limited data

Many thanks

Denis

• ###### Re: Conditional Calculation

Hi

your dimension DayRate is in the table CurrentRates

This table as a drop table statement in your script

use this dimension CurrentRates.DayRate

• ###### Re: Conditional Calculation

sorry but I my not have explained properly. I understand the use if the fully qualified expression but that is not my problem.

I want to create a new masure or column that follows this pseudo code logic

if DayNight = day

sum the kWh for that selection or filter and multiply by currentRates.dayrate

else the same calculation but multiplied by NighRate.

does that make sense. I can't get either "if" or set expression to work

many thanks

DEnis

• ###### Re: Conditional Calculation

Hi

Try this

as new mesure :

if(DayNight='Day',

(sum((kWh )* /100)),

(sum((kWh )* /100))

)

Bruno

• ###### Re: Conditional Calculation

Bruno - thanks very much. This works just as I need.

Best regards and Bonnes Fêtes.

Denis

• ###### Re: Conditional Calculation

Hi

Joyeux Noël et Bonne fêtes également,

Bruno

• ###### Re: Conditional Calculation

Hi, I'm back, I'm afraid.

Bruno's expression worked perfectly in my Debug table but I'm mystified as to why it doesn't work if I split it in two. Let me explain:

The below surely should produce just the Day Charge

if(DayNight='Day',

(sum((kWh )*x /100))

and the following the Night Charge

if(DayNight='Night',

(sum((kWh )*y /100))

but each produces a zero result. Why?

Also, I'm finding that if I put the full expression in, say, a KPI, it also produces zero.

I'm confused but probably missing the obvious.

Thanks,
Denis

• ###### Re: Conditional Calculation

Hi

If I  well understand you want to calculate the sum of KWh depending of the day price or night price by Site

I tested in your app this mesure but missed to send you back :

So

if(

DayNight='Day',

(sum((kWh )* [CurrentRates.DayRate]/100)),

(sum((kWh )* [CurrentRates.NightRate]/100)))

See below the table i get :

So

DayNight :

=DayNight

Rate = calculated dimension

if(DayNight='Day',[CurrentRates.DayRate],[CurrentRates.NightRate])

Sum(KWh) = Sum(kWh)

Elec Cost 2 :

if(

DayNight='Day',

(sum((kWh )* [CurrentRates.DayRate]/100)),

(sum((kWh )* [CurrentRates.NightRate]/100)))

In that case X and Y from your first mesure are corresponding to [CurrentRates.DayRate] and [CurrentRates.NightRate] is that OK or did I  missunderstood something ?