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: 
tmumaw
Specialist II
Specialist II

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

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

View solution in original post

6 Replies
Not applicable

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
Specialist II
Specialist II
Author

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

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
Specialist II
Specialist II
Author

Only YTD, MTD is working fine.

Not applicable

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
Specialist II
Specialist II
Author

Thanks.  Everything works perfect.