Formula Question

[#1]

If i was looking to implement an or statement for a specific month within a dataset, how would i write that? My or statement doesnt seem to be working so i havent yet added in a month...

So far i have:

=If([Sales Office] = 'Canada' or [Sales Office] = 'USA', sum(NetDollars))

Your help is appreciated!

Thanks,

Parrish

[jerem1234]

Why not some set analysis:

sum({<[Sales Office] = {'Canada', 'USA'}>}NetDollars)

If that won't work, could you elaborate a little more?

Hope this helps!

[Anonymous]

Parrish,

The correct way is

sum(If([Sales Office] = 'Canada' or [Sales Office] = 'USA', NetDollars))

or better

sum(If(match([Sales Office],'Canada','USA'), NetDollars))

Regards.

Michael

[jpapador · Accepted solution]

Try:

Sum({$<[Sales Office]={'Canada', 'USA'}, Month={'Jan'}>} NetDollars)

You could also make month dynamic by doing the following:

Sum({$<[Sales Office]={'Canada', 'USA'}, Month={$(=Max(Month)-n)}>} NetDollars)

Where N is how many months forward or back you want.  If you just want current month then you dont need the n portion.

[aveeeeeee7en]

Hi Parrish

You can't write expressions on your own ways. They have a proper format.

Use any of the below mentioned codes:

1) =Sum(If(([Sales Office] = 'Canada' OR [Sales Office] = 'USA') AND (Month='Feb'),NetDollars))

2) =Sum(If(wildmatch([Sales Office],'Canada','USA'),NetDollars))

3) =Sum({<[Sales Office] = {'Canada', 'USA'},Month='Feb'>}NetDollars)

Regards

Aviral Nag

[#6]

Do you have to use set analysis instead of an ‘or’ statement? I know I would need to use it to specify a month, but I know I have used an ‘or’ statement in a listbox before to show only certain countries. Also, if my date was “last February” , would I just replace ‘Jan’ with that number? I feel like maybe I would need to do a date formula…

[jpapador]

In response to your first question.  You couldve either used set analysis or an if statement, both wouldve worked.  As for your requirement to see last February, do you always want to see 12 months ago or does this change?  If it is always "this month but from last year" you could work that logic into your set analysis (or if statement).

[#8]

Thanks Jared! I played around with both ways and they both give me what I wanted. I appreciate the help!