Prior-Year figures with consideration of current selections

[#1]

According to Rainer suggestions of using a range of date to get YTD figures for actual and prior year, unfortunately this results in another problem.

I have coded the following two formulas:

Actual Year:

=sum ( { $< Invoicedate={">=$(=yearstart(MaxOrderDate)) <=$(=MaxOrderDate)"}, costumerType={Third}>} SalesAmount)

Prior Year

=sum ( { $< Invoicedate={">=$(=addyears(yearstart(MaxOrderDate),-1)) <=$(=addyears(MaxOrderDate,-1))"}, costumerType={Third}>} SalesAmount)

This works great when i have no selections, so I get the 2010 figures until e.g. 24.9.2010 and also the 2009 figures until 24.9.2009,

BUT when i choose a year the Prior Year column always stays empty, doesnt matter if i choose 2010, 2009 or 2008.

I cant understand that, as WITHOUT Selections he brings 2009 figures as PRIOR year, but when i choose 2010 he DOES NOT BRING me the 2009 figures as PRIOR year, i can't follow his logic.

Hope you can help me once more guys,

thanks a lot in advance,

Christoph Peukert

[#2]

no that has nothing to do with it, i just included it to show that i have to manage three criterias in one formula

so from your point of view the year-part in my formula should work?

[jonathandienst · Accepted solution]

Christoff

There is no AddYears function in QV. Use AddMonths instead:

=sum({ $< Fakturadat={">=$(=AddMonths(yearstart(MaxOrderDate),-12)) <=$(=AddMonths(MaxOrderDate,-12))"}>} SalesAmount)

...also fixed a missing ). Assumes that Fakturadat is a date field in the default date format for your model.

Try this one.

Jonathan

[#4]

Yeah THAT is exactly the formula i was searching for, thank you very much for your effort Jonathan!

but for your information: Addyears-function DOES EXIST and also work! 🙂

[johnw]

Christoph_Peukert wrote:Addyears-function DOES EXIST

Well, so it does! It's not in my documentation, but it works. I'll have to keep that in mind.

[Anonymous]

I'm having a similar issue, but can't get the prior years information to work. This is the expression I'm using and a value of 0 is generated. Any help would be greatly appreciated.

Count({$<Year = {$(=max(Year)-1)}, Month = {"<=$(=max({<Year={$(=max(Year))}>} Month))"}>} [Act #])