topic Calculation (Set Analysis or If) in QlikView

Calculation (Set Analysis or If)

tmumaw — Mon, 05 Dec 2011 18:34:56 GMT

Need a little help. I am trying to calculate our dollar value based on an exchange rate. Having a few problems. It does not like my statement. Basically if my MTD flag is 1 and company 3000 use exchange rate in calculation otherwise value times -1.

Thanks

sum({<MTDFlag = {1},[Company Code_RBUKRS] = {3000}>} ([In company code currency_HSL] * [Exchange Rate] * -1),([In company code currency_HSL] * -1))

Calculation (Set Analysis or If)

Mon, 05 Dec 2011 18:44:07 GMT

I think what you need is this:

sum(if(MTDFlag =1 and [Company Code_RBUKRS] = 3000,

([In company code currency_HSL] * [Exchange Rate] * -1),

([In company code currency_HSL] * -1)))

Calculation (Set Analysis or If)

tmumaw — Mon, 05 Dec 2011 20:44:07 GMT

Having problems now with YTD.....tried using same calculation and numbers match mtd, Using same formula only changed MTD to YTD....any ideas?

Calculation (Set Analysis or If)

Tue, 06 Dec 2011 12:00:45 GMT

So do you want an expression that ONLY returns YTD, or an expression that returns one result for YTD and another result for everything else?

Calculation (Set Analysis or If)

tmumaw — Tue, 06 Dec 2011 13:13:00 GMT

Only YTD, MTD is working fine.

Calculation (Set Analysis or If)

Tue, 06 Dec 2011 13:25:07 GMT

That's not what I meant, I mean did you want an expression similar to your original question? Where if the results fall in YTD then do one calculation, otherwise do another? If this is the case you would use and IF THEN ELSE statement.

If you want to return ONLY data that falls in YTD then you can use set analysis. How is YTD Determined? If you are basing it off of a date field you should be able to use something like this:

Sum({$<Year={$(=Year(Today()))}>}

(if( [Company Code_RBUKRS] = 3000,

([In company code currency_HSL] * [Exchange Rate] * -1),

([In company code currency_HSL] * -1)))

)

Where Year is equal to Year(DATEFIELD)

Calculation (Set Analysis or If)

tmumaw — Tue, 06 Dec 2011 13:47:32 GMT

Thanks. Everything works perfect.