[RESOLVED] current year vs prior year

[tradeex]

Hello -

I have a relational model where I have a prompt for month and a prompt for year. In the report I need to show current month and same month prior year. current month shows fine but not prior year.
my Month calcs look like:

month = tOTAL(IF( [VERSION ID]='ACTUAL') THEN ([AMOUNT]) ELSE (0))

PY month = tOTAL(IF( [VERSION ID]='ACTUAL' and [year]=?year? - 1) THEN ([AMOUNT]) ELSE (0))
but PY month doesn't work.

My year prompt is the basic prompt. Please help been working on this for past 2 days and can't get it to work.

Thanks

[Virgin]

Hello,

You use the parameter ?Year? - 1
if you want to have the prior year , try with :

PY month = total(if([VERSION ID] = 'ACTUAL' and [year] = (cast(?year?; integer) - 1) then ([AMOUNT]) else (0))

[Robl]

The code looks fine.
Are you filtering the data based on the year and month?

[tradeex]

Yes I am filtering the data based on year and month. So the user will select month = JAN and Year = 2015 and report should show Jan Month Sales, YTD 2015 and prior year Jan 2014. And the prior year is giving me issues since year = 2015.

[tradeex]

I tried the

total(if([VERSION ID] = 'ACTUAL' and [year] = (cast(?year?; integer) - 1) then ([AMOUNT]) else (0))

but gave me parsing errors. Did not work. What am I missing?

[MFGF]

Yes I am filtering the data based on year and month. So the user will select month = JAN and Year = 2015 and report should show Jan Month Sales, YTD 2015 and prior year Jan 2014. And the prior year is giving me issues since year = 2015.

What filter syntax are you using? I'm guessing your filter for year is [Your year item] = ?YearParameter? - can you confirm? If so, that's where your issue lies.

MF.

[tradeex]

Hi -

My filter is like this :

[YEAR] in (?year?)

[MFGF]

Hi -

My filter is like this :

[YEAR] in (?year?)

So if the user selects (say) 2015 from the prompt, this is then substituted into the parameter so your filter ends up as being [YEAR] in (2015)

Hopefully you can see that this will limit the rows coming into your report to only those for 2015, so anything you try to calculate for 2014 will not have any supporting data in the query.

MF.

[tradeex]

I was able to figure it out. I had to change my filter to do between ?year? and ?year? - 1 and adjust my data item calculations