topic Re: General Expression Help in QlikView

General Expression Help

jcampbell474 — Thu, 03 Aug 2017 12:01:37 GMT

I’m having a problem getting an expression to work. It works in other objects where an ‘if’ statement isn’t used. Can someone help me understand how I should implement it?

Context:

A change has been made that requires two products (HO and DF) be removed from the Southern and Western regions. I use this in other objects and it works fine:

Here is the original expression (which works fine) that I need to change/update:

=if(sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))}>} sold_cnt) -
sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound)-1)}>} sold_cnt)>0,
sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))}>} sold_cnt) -
sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound)-1)}>} sold_cnt))

What I’m trying to accomplish. It says Expression OK and doesn’t identify any errors, but the object says Garbage after expression: “,”.

=if(Region='Western' or Region='Southern',sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))},product-={'HO','DF'}>} sold_cnt),
sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))}>} sold_cnt)) -
if(Region='Western' or Region='Southern',sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))},product-={'HO','DF'}>} sold_cnt),
sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))}>} sold_cnt)) > 0,
if(Region='Western' or Region='Southern',sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))},product-={'HO','DF'}>} sold_cnt),
sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))}>} sold_cnt)) -
if(Region='Western' or Region='Southern',sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))},product-={'HO','DF'}>} sold_cnt),
sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))}>} sold_cnt))

Any and all help will be greatly appreciated.

Re: General Expression Help

sunny_talwar — Thu, 03 Aug 2017 12:09:45 GMT

Do you have Region as one of the dimension where you are using this expression?

Re: General Expression Help

jcampbell474 — Thu, 03 Aug 2017 12:17:49 GMT

No, Region isn't a dimension. I only have two calculated dimensions.

=Agency_nbr&' ('&product&')'

=AgencyName&' ('&City&')'

Re: General Expression Help

sunny_talwar — Thu, 03 Aug 2017 12:20:37 GMT

I guess City can only belong to one and only one region?

Re: General Expression Help

jcampbell474 — Thu, 03 Aug 2017 12:24:34 GMT

That is correct.

Re: General Expression Help

sunny_talwar — Thu, 03 Aug 2017 12:30:39 GMT

Trying to understand why are you subtracting the same expression from the same expression

=if(Region='Western' or Region='Southern',sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))},product-={'HO','DF'}>} sold_cnt),
sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))}>} sold_cnt)) -
if(Region='Western' or Region='Southern',sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))},product-={'HO','DF'}>} sold_cnt),
sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))}>} sold_cnt)) > 0,

The green and the red are the same expression... isn't it?

Re: General Expression Help

jcampbell474 — Thu, 03 Aug 2017 12:35:27 GMT

Ah, yes, it is. I didn't copy and paste it correctly.

The second (green) expression should have {$(=max(FiscalYearWeekBound)-1)} instead of {$(=max(FiscalYearWeekBound))}>}.

Here is the initial expression:

It's subtracting the previous week from the current week if the difference is >0.

Re: General Expression Help

sunny_talwar — Thu, 03 Aug 2017 12:40:13 GMT

Let's try this

=If(Match(Region, 'Western', 'Southern'),

If(Sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))}, product-={'HO','DF'}>} sold_cnt) -
Sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound)-1)}, product-={'HO','DF'}>}sold_cnt) > 0,
Sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))}, product-={'HO','DF'}>} sold_cnt) -
Sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound)-1)}, product-={'HO','DF'}>} sold_cnt)),

If(Sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))}>} sold_cnt) -
Sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound)-1)}>}sold_cnt) > 0,
Sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))}>} sold_cnt) -
Sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound)-1)}>} sold_cnt)))

Re: General Expression Help

jcampbell474 — Thu, 03 Aug 2017 13:15:17 GMT

Thank you, Sunny. It works perfect. I didn't think of the Match approach. Great idea!!

I'm sorry to bother you with this, but I thought that if I could get this one worked out I could change a value and it would work in another table. This one is for 'Gainers' (sum > 0), compared to the previous week.

I also need 'Losers' (sum <=0), compared to the previous week. I simply changed '>0' in your expression to '<=0' and it drops the match. Dimensions and everything else is the same. Any idea what could be happening?

=If(Match(Region, 'Western', 'Southern'),
If(Sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))}, product-={'HO','DF'}>} sold_cnt) -
Sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound)-1)}, product-={'HO','DF'}>}sold_cnt) <= 0,
Sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))}, product-={'HO','DF'}>} sold_cnt) -
Sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound)-1)}, product-={'HO','DF'}>} sold_cnt)),

If(Sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))}>} sold_cnt) -
Sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound)-1)}>}sold_cnt) <= 0,
Sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))}>} sold_cnt) -
Sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound)-1)}>} sold_cnt)))

Re: General Expression Help

sunny_talwar — Thu, 03 Aug 2017 13:17:27 GMT

Don't see anything wrong here syntax wise... what is the issue with this? Giving incorrect numbers?

Re: General Expression Help

jcampbell474 — Thu, 03 Aug 2017 14:46:45 GMT

It includes products HO and DF in the results. Seems like the Match condition isn't met.

I think I figured out what's going on. Agencies can sell more than one product. It doesn't happen in the Gainers table, only in the Losers. Since Product is a dimension, should the expression contain aggregation?

Re: General Expression Help

sunny_talwar — Thu, 03 Aug 2017 14:57:24 GMT

Are you saying that you want to exclude any agency which sells HO or DF?

Re: General Expression Help

jcampbell474 — Thu, 03 Aug 2017 15:05:08 GMT

Yes. But, that same agency can sell PPA, CV, or MC.

product-={'HO','DF'}

Re: General Expression Help

sunny_talwar — Thu, 03 Aug 2017 15:07:54 GMT

So, do you want to put all Agencies into one bucket if one of there products is HO or DF? or do you not care about the Agency and look at the product on each row? If Agency ABC has PPA and HO, do they go in one bucket because one of the two products was HO or do they go in different buckets (HO is one and PPA in other)?

Re: General Expression Help

jcampbell474 — Thu, 03 Aug 2017 15:11:40 GMT

Different buckets. Agency+Product = Dimension (bucket)

Re: General Expression Help

jcampbell474 — Thu, 03 Aug 2017 19:08:32 GMT

It was simple. Just some errant parenthesis. Removed the blue and placed the red.

Thank you again for the help!

If(Match(Region,'Western','Southern'),

If(Sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))}, product-={'HO','DF'}>} sold_cnt) -
Sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound)-1)}, product-={'HO','DF'}>}sold_cnt) <= 0,
Sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))}, product-={'HO','DF'}>} sold_cnt) -
Sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound)-1)}, product-={'HO','DF'}>} sold_cnt)),

If(Sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))}>} sold_cnt) -
Sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound)-1)}>}sold_cnt) <= 0,
Sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound))}>} sold_cnt) -
Sum({<FiscalYearWeekBound={$(=max(FiscalYearWeekBound)-1)}>} sold_cnt))))

Re: General Expression Help

sunny_talwar — Thu, 03 Aug 2017 19:10:17 GMT

Great