topic Re: Expression Help! in QlikView

Expression Help!

james_butler — Fri, 25 Oct 2013 14:59:39 GMT

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

Re: Expression Help!

swuehl — Fri, 25 Oct 2013 15:04:02 GMT

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)

Re: Expression Help!

jvitantonio — Fri, 25 Oct 2013 15:13:22 GMT

Hi,

Is your period month a numeric value?

Re: Expression Help!

james_butler — Fri, 25 Oct 2013 15:16:47 GMT

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;

Re: Expression Help!

jvitantonio — Fri, 25 Oct 2013 16:36:03 GMT

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.