Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

11 Replies
Not applicable
Author

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

brunobertels
Master
Master

Hi

Hope this will help you

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

add this mesure

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 ?