topic Re: Formula Question in QlikView

Formula Question

Tue, 25 Feb 2014 18:40:27 GMT

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

Re: Formula Question

jerem1234 — Tue, 25 Feb 2014 18:47:31 GMT

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!

Re: Formula Question

Anonymous — Tue, 25 Feb 2014 18:48:22 GMT

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

Re: Formula Question

jpapador — Tue, 25 Feb 2014 18:49:18 GMT

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.

Re: Formula Question

aveeeeeee7en — Tue, 25 Feb 2014 18:58:25 GMT

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

Re: Formula Question

Tue, 25 Feb 2014 18:59:20 GMT

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…

Re: Formula Question

jpapador — Tue, 25 Feb 2014 19:36:12 GMT

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

Re: Formula Question

Tue, 25 Feb 2014 19:57:33 GMT

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