Set Expression Help

[m4u]

Hello

I have a weird issue - see attached example

When I do a set expression by fixed value (2009) everything is ok

When I do a set expression with a condition by the dimension field - it returns 0

Im obviously missing something..

[#2]

Hi,

Set analysis works fine with a single value, in your example "GoalYear=SaleDateY", QlikView does not know with which valie to campare "GoalYear" even though "SalesdateY" has 29 times the same value "2009".

=SUM(if(GoalYear=SaleDateY,GoalQuantity,0)) would give you a value but is not good regarding performance issues.

A solution would be to be able to create a flag in your load script and use the flag to calculate your expression.

C. Lee Baw

Business & Decision

[m4u]

Hi Why it doesnt know which values to sum up? Each cell in the pivot corresponds to certain dimensions in the pivot, so what's the difference (from QV point of view) between the expression which you wrote, and the SET ANALYSIS one? Thanks

[pdumas]

Hi,

I changed to

=SUM({$<GoalYear={$(=SaleDateY)}>}GoalQuantity)
and the result is what you expect

The magic of set analysis syntaxis!

Pierre.

[m4u]

Hi

It does work 🙂

1. Could you please explain the syntax? Since I dont seem to understand why what I did was wrong according to QV's documentation

2. Actually I need to do this by more then 1 field - and

SUM({$<GoalYear={$(=SaleDateY)}, GoalMonth={$(=SaleDateM)}>}GoalQuantity)

doesn't work...

Thanks

[m4u]

Anybody?

[Miguel_Angel_Baeyens]

Hello,

Syntax seems to be all right, provided SaleDateY contains a year (so it does according to your previous post) and SaleDateM contains a month in the same way (text, number) it is stores in GoalMonth field. If not, set analysis would return unexpected values... What do you have in SaleDateM variable and how do you soter you months in GoalMonth field?

[m4u]

I have month number (1..12) - I load it from excel

The strange thing is that if I use any of the expressions separetely, it works (for one field) - but not combined.

I'm quite sure this is a pretty typical scenario when there is no association by all fields and such calculation needs to be made.. like Budget vs Actual by multiple dimensions..

If there are any alternative solutions for this scenario Ill be glad to hear..

[#9]

I have been using set analysis right from the start, the $ sign expansion seems to work slightly differently since the introduction of indirect set analysis, for example

$(=max(SaleDateY)) should return 2010 however I have had to get round this referencing a variable so that a variable brings back a single value

$(vSaleDateY) and do the calc in the variable

Please see worked example below

count({$< Bkg_Date = {"<=$(vDateYr-3)"} >}Customers)

Variable

vDateYr-3 =max(Cal.Year)-3

hope this is of help

Regards,

Neil

[#10]

SUM({$<GoalYear={$(=SaleDateY)}, GoalMonth={$(=SaleDateM)}>}GoalQuantity)

The problem is that SalesDateM has 7 possible values and you are trying to force a selection without the user having made a selection. SaleDateY is going to have the same problem once more than 1 year becomes available. Are you wanting the formula to work accross all possible values? In other words, should GoalMonth select all values in SaleDateM (2, 3, 4, 5, 6, and 7) until a selection is made for one or more months? If so, you have to provide a mechanism for the set expression to see all possible field value through a concatenation.

SUM({$<GoalYear={$(=concat(chr(39) & SaleDateY & chr(39), ','))}, GoalMonth={$(=concat(chr(39) & SaleDateM & chr(39), ','))}>} GoalQuantity)

In this formula, GoalYear and GoalMonth are handed all possible values of SaleDateY and SaleDateM in a comma separated list. Now no matter what is selected in SateDateY and SaleDateM (single OR multiple selections), you will have the formula calculating for all possible values.