Problem with a simple Set Expression

[#1]

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?

[#2]

try like below,

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

[MK_QSL]

try

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

[#4]

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.

[Miguel_Angel_Baeyens]

Hi Tobias,

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

Miguel

[#6]

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 '-'

[Peter_Cammaert]

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...

[Peter_Cammaert]

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?

[#9]

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.

[#10]

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])

OR

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.