topic Re: Set expression behaves unexpected when multiplying with a dimension value in QlikView

Set expression behaves unexpected when multiplying with a dimension value

wdonckers — Mon, 24 Mar 2014 14:30:55 GMT

Hi,

I would like to show all Months' values up to and including the Month that is selected.

for instance when I select 2014 and Feb i should get

Month Amount

Jan 100

Feb 220

I tried using the following expression:

Sign *

sum({$<

[Value Type for Reporting_VTYPE]={'010'},

Version_VERSION=,

Month={"<=$(=max({<Year={$(=max(Year))}>} Month))"},

Quarter=,

Year = {$(=max(Year))}

>}[Sales for the Period_SALES])

the result is :

Month Amount

Feb 220 So only the month selected is shown, and the <= selection seems to have no effect.

The "Sign" is a field in the datamodel than contains a 1 or minus 1 in order to change the sign depending on the reporting line.

In my example I would expect it to work because Sign is unambiguously known (I see that when including it as a dimension)

If I leave the "Sign *" part out then the result is :

Month Amount

Jan -100

Feb -220

--> the months are ok but the signs are not.

So, can a calculated set expression not be used in another calculation?

What is the reason behind this?

thanks for sharing your view with me.

rgds,

Wim

lironbaram — Mon, 24 Mar 2014 14:37:18 GMT

sign is your problem

because when you select February

all records in jan don't have records in sign field so you need to put sign inside the set expression

sum({$<

[Value Type for Reporting_VTYPE]={'010'},

Version_VERSION=,

Month={"<=$(=max({<Year={$(=max(Year))}>} Month))"},

Quarter=,

Year = {$(=max(Year))}

>}[Sales for the Period_SALES]*sign)

MK_QSL — Mon, 24 Mar 2014 14:38:46 GMT

Try something like below...

Month = {"<=$(=Max(Month))"}, Year = {'$(=Max(Year))'}