Qlikview Case When

[#1]

Hi all,

Being dumb here, but looking for Case When logic in a qlikview expression.I'm building a proof of concept and want to incorporate, for example, a target which varies by month. I have loaded a table with dates in column a which has the first data of the month, and a value in the second column (below is uk, so d,m,y). Effectively I want an expression that returns 50 if the current month is January. The easy bit was testing the logic with if(TargetDate = MonthStart(Today())) which I can get to evaluate to true, but I need the following logic which pulls through the target value for the current month. Following through with ',Target' in the IF statement doesnt work obviosuly.

TargetDate     Target

1/1/15             50      

1/2/15             60

cheers,

[tresB]

May be like:

Month(TargetDate)=Month(Today())

[jpenuliar]

If you have fixed Target Values for each Months, I suggest you put them in a table and use the concept of ApplyMap() to your TargetDate.

[#4]

Thanks, though my conditional logic is fine in terms of identifying IF the selected targetdate is in the current month, though will only evaluate selected targetdates. I want to return the target value based on the current month without having to having to select a targetdate.

[#5]

I'll have a look into apply map and get back.

[#6]

Hi Jonathan, excuse me if I have misunderstood, though my target table wont be related to any other table when loaded. It is effectively stand alone so that I can set the current month's target to a variable based on matching the current month to the target table.

[jpenuliar]

if you only need to set the CurrentMonth's Target value, I think the apply Map is overkill.

you can do away with tresesco 's comment earlier and i quote.

"May be like:

Month(TargetDate)=Month(Today()) "

you can probably have a field with logic below:

if  Month(TargetDate)=Month(Today()) then TargetValue else nothing

[jpenuliar]

where TargetValue is your variable

[tresB · Accepted solution]

If you put the comparison logic in an aggregation function like:

Sum(If(Month(TargetDate)=Month(Today()), AmountField)

It should work without having to select a date.

[#10]

Perfect, thank you. Placing in an aggregate function was the answer.

I changed the logic slightly to enable me to load numerous years values to =Sum(If((TargetDate)=MonthStart(Today()), TargetValue))