topic Re: Problem with a simple Set Expression in QlikView

Problem with a simple Set Expression

Fri, 20 Mar 2015 11:22:55 GMT

Following advice from literature and forum contributions I have created a master calendar with fields

Period

Year

Month

Year-Month

Quarter

PeriodID

where 'Period' is simply YYYYMM linking it to an identical field in the main data table.

The field 'PeriodID' is autonumbering the records to create a unique identifier.

My app has a Multi Box for the selction of a customer and a month.

What I have observed is that my expression to calculate the sales for a customer in the previous month

Sum({$< PeriodID = {$(=Max(PeriodID)-1)}, Year = , Quarter = , Month = , Period = >}[Sales])

only returns a value >0 when I use the fields 'Period' or 'PeriodID' for selections in the Multi Box. When I use the field 'Year-Month' the result of the expression is 0.

I have validated that Max(PeriodID)-1 returns a correct value.

Any idea where the problem is?

Re: Problem with a simple Set Expression

Fri, 20 Mar 2015 11:26:46 GMT

try like below,

PeriodID = {"$(=Max(PeriodID)-1)"},

Re: Problem with a simple Set Expression

MK_QSL — Fri, 20 Mar 2015 11:29:24 GMT

try

Sum({$<Year-Month = , PeriodID = {$(=Max(PeriodID)-1)}, Year = , Quarter = , Month = , Period = >}[Sales])

Re: Problem with a simple Set Expression

Fri, 20 Mar 2015 11:41:00 GMT

Thanks for the quick responses.

Unfortunately in both cases, i.e.

PeriodID = {"$(=Max(PeriodID)-1)"},

and {$<Year-Month = , PeriodID = {$(=Max(PeriodID)-1)}, ..

returned '-' instead of '0' or the correct sum.

Re: Problem with a simple Set Expression

Miguel_Angel_Baeyens — Fri, 20 Mar 2015 11:42:32 GMT

Hi Tobias,

Since you are using the Max() function, use "{1" in your set analysis instead of the current "{$"

Miguel

Re: Problem with a simple Set Expression

Fri, 20 Mar 2015 11:52:40 GMT

Thank you for our advice but unfortunately when I try

=Sum({1< PeriodID = {$(=Max(PeriodID)-1)}, Year = , Quarter = , Month = , Period = >}[Sales])

it returns a wrong result, i.e. not the expected sum

When I try

=Sum({$< PeriodID = {1(=Max(PeriodID)-1)}, Year = , Quarter = , Month = , Period = >}[Sales])

the result is '-'

Re: Problem with a simple Set Expression

Peter_Cammaert — Fri, 20 Mar 2015 12:33:42 GMT

The second expression is invalid. You replaced a $-sign that belonged to a dollar-substitution expression (like $(var) ), and that was not a set specifier like the one in the front. $-signs are a bit schizophrenic here...

Re: Problem with a simple Set Expression

Peter_Cammaert — Fri, 20 Mar 2015 12:41:07 GMT

So what you are doing is, when you select Period=201501' with autonumber value = 13, you intend to display the Sales sum for the period identified by PeriodID = autonumber value 12, correct? You are sure that PeriodID = 12 has Sales data, when you take into account that other selections (unknown to us) may reduce away all Sales data?

Re: Problem with a simple Set Expression

Fri, 20 Mar 2015 13:14:23 GMT

Yes, there is data for PeriodID=12

Fortunately my current set of data ranges

for Period from 201401 to 201412

and for PeriodID (autonumbered) from 1 to 12.

I have validated that there is data for every period.

I get correct results when I use 'Period' or 'PeriodID' in the Multi Box but unfortunately these expressions are not user-friendly so I'd like to use 'Year-Month' ... but it returns 0.

Re: Problem with a simple Set Expression

Fri, 20 Mar 2015 15:39:54 GMT

Hi..have you tried something like below : ignoring selection in year-month but removing Period =

Sum({$< PeriodID = {$(=Max(PeriodID)-1)}, Year = , Quarter = , Month = , Year-Month= >}[Sales])

Sum({$< PeriodID = {$(=Max(PeriodID)-1)}, Year = , Quarter = , Month = >}[Sales])

or since you mentioned period being the linking field something like:

Sum({$<Period={$(=Max({$<PeriodID = {$(=Max(PeriodID)-1)}>}Period))},Year = , Quarter = , Month = , Year-Month= >}[Sales])...

if these dont work you could try to check out the unique monthid in this post linked below and create it in you calendar and fact table as the linking field instead of using Period:

The Magic of Set Analysis – Point In Time Reporting | iQlik - Everything QlikView.

Re: Problem with a simple Set Expression

Anonymous — Fri, 20 Mar 2015 16:56:57 GMT

I believe tis expression is correct, only the field name must be in brackets:

..."Year-Month"=,...