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: 
Aspiring_Developer
Creator III
Creator III

Adding two values with respect to date conditions at backed script

Hello everyone,

I am trying to achieve the below:-

If("Calendar Year" <= Year(today()), Actual) + if("Calendar Year" >=year(today()), RB) as Hours

 

Is this correct to calculate the dimension at backend.

 

I want to create a dimension "Hours" using this conditon

Hours = “actual” hours when Year < Today + “RB” hours when Year >= Today

 

Pleas help

Labels (3)
2 Solutions

Accepted Solutions
Kushal_Chawda

Assuming that you have all column in same table then try below in script You can then use the hour as a dimension

If("Calendar Year" <= Year(today()), Actual, RB) as Hours

View solution in original post

sunny_talwar

Not entirely sure, but may be this

RangeSum(
If("Calendar Year" < Year(Today()), Actual),
If("Calendar Year" > Year(Today()), RB)
) as Hours,

But what happens when Calendar year = Year(Today())? Right now both you are if statements don't include that condition. You might want to change one of the if statement logic to include =, either >= or <= depending on the value needed for current year

View solution in original post

9 Replies
Taoufiq_Zarra

can you share a sample data and if possible the expected output ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Kushal_Chawda

You an create a Flag in script and then you can use it to create the measure

LOAD *,

if("Calendar Year" <= Year(today()), 1,2)  as Year_Flag

FROM Source;

then you can use below measure in chart

=sum({<Year_Flag={1}>}Actual)+sum({<Year_Flag={2}>}RB)

Aspiring_Developer
Creator III
Creator III
Author

@Taoufiq_Zarra 

Please see the data:-

Aspiring_Developer_0-1598440667825.png

LOAD

Actual,
If("Calendar Year" < Year(today()), Actual)  + if("Calendar Year" > year(today()), RB) as Hours,
CB,
"CB - Unit",
RB

From....

Please help as i have deliver it next week

 

 

Aspiring_Developer
Creator III
Creator III
Author

@Kushal_Chawda 

 

Mnay thanks Kush for your response.

But i have to create a dimension  as "Hours" using this calculation at the backend. Could you please help me on that.

 

Many thanks again

Kushal_Chawda

Assuming that you have all column in same table then try below in script You can then use the hour as a dimension

If("Calendar Year" <= Year(today()), Actual, RB) as Hours

Aspiring_Developer
Creator III
Creator III
Author

@Kushal_Chawda 

Hello Kush , i wish to achieve this:

Hours = “actual” hours when Yearmonth < Today + “RB” hours when Yearmonth >= Today

We have to add them at the backend and display them as "Hours" in dimension.

 

Thanks

Aspiring_Developer
Creator III
Creator III
Author

Can anyone please help on this as it is very critical for me and i tried everything but not working.

 

@sunny_talwar 

Kushal_Chawda

It's really not clear what you are trying to do. did you check my last response?

sunny_talwar

Not entirely sure, but may be this

RangeSum(
If("Calendar Year" < Year(Today()), Actual),
If("Calendar Year" > Year(Today()), RB)
) as Hours,

But what happens when Calendar year = Year(Today())? Right now both you are if statements don't include that condition. You might want to change one of the if statement logic to include =, either >= or <= depending on the value needed for current year