Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
Hi,
Is your period month a numeric value?
JV
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;
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