topic Re: Qlikview Case When in QlikView

Qlikview Case When

Tue, 06 Jan 2015 11:27:00 GMT

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,

Re: Qlikview Case When

tresB — Tue, 06 Jan 2015 11:31:15 GMT

May be like:

Month(TargetDate)=Month(Today())

Re: Qlikview Case When

jpenuliar — Tue, 06 Jan 2015 11:31:34 GMT

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.

Re: Qlikview Case When

Tue, 06 Jan 2015 11:35:55 GMT

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.

Re: Qlikview Case When

Tue, 06 Jan 2015 11:36:16 GMT

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

Re: Qlikview Case When

Tue, 06 Jan 2015 11:38:51 GMT

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.

Re: Qlikview Case When

jpenuliar — Tue, 06 Jan 2015 11:45:08 GMT

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

Re: Qlikview Case When

jpenuliar — Tue, 06 Jan 2015 11:45:48 GMT

where TargetValue is your variable

Re: Qlikview Case When

tresB — Tue, 06 Jan 2015 11:49:30 GMT

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.

Re: Qlikview Case When

Tue, 06 Jan 2015 11:53:06 GMT

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))