Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

tmumaw
Valued Contributor

Calculation (Set Analysis or If)

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

1 Solution

Accepted Solutions
Not applicable

Calculation (Set Analysis or If)

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

6 Replies
Not applicable

Calculation (Set Analysis or If)

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

tmumaw
Valued Contributor

Calculation (Set Analysis or If)

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

Not applicable

Calculation (Set Analysis or If)

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?

tmumaw
Valued Contributor

Calculation (Set Analysis or If)

Only YTD, MTD is working fine.

Not applicable

Calculation (Set Analysis or If)

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)

tmumaw
Valued Contributor

Calculation (Set Analysis or If)

Thanks.  Everything works perfect.

Community Browser