Expression Help!

[james_butler]

Hi

I'm struggling with the a relatively simply formula, I'm try to get the total for an expense code, for last period.

I've tried both of the below and neither seem to work, can anyone help?

=-Sum({$<PeriodMonth={$(=Only(PeriodMonth-1))}>} {$<PeriodYear={$(=Only(PeriodYear))}>} {$<ExpenseCode = {50000}>}NetValue)

=-Sum({$<PeriodMonth={$(=Only(PeriodMonth)-1)}>} {$<PeriodYear={$(=Only(PeriodYear))}>} {$<ExpenseCode = {50000}>}NetValue)

My formula for this period works fine.....=-Sum({$<PeriodMonth={$(=Only(PeriodMonth))}>} {$<PeriodYear={$(=Only(PeriodYear))}>} {$<ExpenseCode = {50000}>}NetValue))

Thanks

James

[swuehl]

I think your field modifier should look like

$(=Only(PeriodMonth)-1)

I also think that you shouldn't use multiple set expressions, try instead

=-Sum({$<PeriodMonth={$(=Only(PeriodMonth)-1)}, PeriodYear={$(=Only(PeriodYear))}, ExpenseCode = {50000}>}NetValue)

[jvitantonio]

Hi,

Is your period month a numeric value?

JV

[james_butler]

Hi JV

I'm not sure, I'm very new to QlikView.... My Script is below

SQL SELECT CostCentreCode,
ExpenseCode,
Period,
2000 + LEFT(Period,2) as PeriodYear,
RIGHT(Period,2) as PeriodMonth, 
NetValue
FROM F8LIVE.dbo.F8NLTRANS;

[jvitantonio]

Hi, what's the value inside "Period"? Give me an example of the data contained in that field.

I guess Period is something like 13/10 with format "YY/MM"?

Be careful the way you use your expression, as it will not work in January. Ex: 01 - 1 = 0 and not 12

You can create a sequential month field like:

...

YEAR*12+MONTH AS YearMonthSequential.

This will give you a unique and sequential value for each YearMonth. Then it's easy to do Max(YearMonthSequential)-1

Give it a try and let me know.

JV