Different result in Table : Variable or Expression

[#1]

Hi all,

i have this

Expression =

if($(vCurrentWorkDay)<15,$(vCurrentWorkDay),

(max({$<Year={$(vPrevYear)}, Month={$(vCurrentMonth)}>}Day)

-

(max({$<Year={$(vCurrentYear)}, Month={$(vCurrentMonth)}>}Day)

-

Only({$<Cdr_Day={$(=if(max(Cdr_Day) <= vCurrentDay, max(Cdr_Day), vCurrentDay-1 ))}>} Day))))

and $(Variable) = Expression.

I create a straight table with Market as dimension and two expressions.

In the first i use the Expression

in the second i use the $(Variable)

but the results is not the same.

WHY?

Can i use the $(Variable) ??

The correct result is the column with Expression (Slovakia=17)

Thank You All

M.

[swuehl]

Have you defined your variable expression with a preceding equal sign or without?

http://www.qlikfix.com/2011/06/08/not-all-variables-are-created-equal/

[#3]

Yes, but without equal sign i can't use the variable expression in a set analysis because Qlikview don't calculate the expression in chart!

Variable "vCurrentWorkDay":

if($(vCurrMonthWorkDay)<2,max({$<Brand=, Year={$(=Year(addmonths(Today(),-1)))}, Month={$(vPrevMonth)}>} Day),max({$<Brand=,Cdr_Day={$(=vCurrentDay-1)}>} Day))

Expression in Chart:

sum({$<Year={$(=Only(Year) - 1)}, Day={'<=$(vCurrentWorkDay)'}, Month={$(=Only(Month))}>} Actual))

Can you help me?

Thanks all

[swuehl]

Sorry, I am a little bit confused. Using the equal sign with your variable vCurrentWorkDay should be ok, since you are using the variable in a set analysis set modifer, hence it is only evaluated once per chart.

As I understood your original post (and as I understood the referenced blog post), the issue arise if you are using a variable as expression, which should be evaluated in the context of the dimension.

Can you maybe post a small sample file, this could make it somewhat easier to understand what you are trying to achieve (best with a description of your expected outcomet).

Regards,

Stefan

[#5 · Accepted solution]

Exactly!

The issue arise if i use a variable as expression, which should be evaluated in the context of the dimension.

The Dimension of the chart (table) is "Market", and the esxpression "Actual 2011" is :

sum({$<Year={$(=Only(Year) - 1)}, Day={'<=$(vCurrentWorkDay)'}, Month={$(=Only(Month))}>} Actual))

"vCurrentWorkDay" is not the same for all Markets, and is calculated in a variable:

=max({$<Cdr_Day={$(=vCurrentDay-1)}>} Day))

If i use the "=" in variable expression, the expression is calculated but vCurrentWorkDay is the same for all markets (it's NOT EXACT).

The Actual for Germany is 1067(wrong value) without active selection on market dimensiomn

If I select Germany in Market dimension, the Actual change in 920 (correct value)

Is possible to show correct value(920) in first table without any selection on Markets?

This comes from variable expression vCurrentWorkDay that changes depending on the market.

Here, too, if I select any market, I get the vCurrentWorkDay default (7) but this is wrong!

I would rather get in each row the vCurrentWorkDay correct (Italy=7, Germany=6, ...).

Thanks.

MC

[swuehl]

Your vCurrentWorkDay variable is defined with equal sign, so it will be evaluated in global scope and only the result, the number will be returned when calling the variable.

Try removing the equal sign at the very beginning of the variable definition. You'll see that you will get the correct results per Market now. That's because the call of the variable now returns the variable definition itself, just pasting the text in. Then the expression will be evaluated, and you'll get different results in the dimension scope.

Do you see what I mean?

But removing the equal sign will not solve all your problems. As I said above, a set expression will only be evaluated once, not per dimension value. So you just can't limit the Day field in a set expression depending on the current dimension value.

You probably know

http://community.qlik.com/docs/DOC-1335

and you already created something similar using match() to check the current dimension value and use distinct expressions per dimension value.

I think you should change your distinct expression lines to something like:

...

if (match(Only(Market),'Germany')>0,sum({$<Year={$(=Only(Year) - 1)},  Month={$(=Only(Month))}>} if(Day<=vWDPrevYearGer, Actual)),

...

moving the Day field restriction from the set analysis to an embedded if clause.

But your setting is quite complex (too complex for simple explanations), and I am still struggling with at least two issues:

a) your vWDPrevYearGer should return the number for Germany only, right?

If I just put =vWDPrevYearGer in a textbox, it returns 7. If I select germany, it returns 6. I don't think it should be dependent on selecting Germany, right?

b) If your variables contain aggregation functions like max and you use the above proposed way to include a variable (just a text macro expansion), you will run into problems if you are using the variable in another aggregation (no aggregation inside aggregation function allowed without using advanced aggregation).

Not sure if you need this, but be aware of that behaviour.

I don't have much time to look into this today, but I hope you have some more ideas how to get along.

Have fun,

Stefan