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]

I think I understand what is occuring. Its important to understand that the $ in '= sum({$<....' that Qlikview should work with the current selections. If you select year = 2010 then the current selections are for this year only, so there are no matching rows with year -1 (2009).

Using the value 1 in its place will tell Qlikview to ignore all current selections as the dataset, or you can just tell it to ignore using any selection in year by adding 'Year=' in your original expression.

Regards,

Gordon

[#3]

First, thanks for the fast reply gordon.

Now I understand WHY qv is doing that, but i think your suggestion to solve this is not the right one in this case.

The user should be able to select 2009 as Year, then QV should display 2009 as Actuals and 2008 as Prior Year.

Isn't there any solution to handle this?

Regards,

Christoph

[#4]

... I meant add the 'Year=' to the prior year expression only, not the actual year

Regards,

Gordon

[#5]

Yeah, it is working perfectly!

Just an "1" instead of "$" ... 🙂 it would have been that easy 😞

Thanks a lot for your effort Gordon,

please pick one or two from here (but not the money!!!) : http://i47.photobucket.com/albums/f164/Gasumbel/Geschenke/pralinen3.jpg

Best regards,

Christoph

[#6]

I just saw ... there is one more problem with that formula,

when choosing just one month, then the actual amount is calculating the right amount, but as PRIOR YEAR do not consider the selections, it is always calculating from 1.1. til MaxOrderDate, so always the YTD data instead of the figures of the month

what would be the easiest way to solve that? using an if-function?

Regards

[#7]

As I mentioned, if you stay with the $ instead of 1 then it will use current selections and just adding 'Year=' to the prior year expression (to ignore any current year selected) then it will honour the selected month.

Tricky blighters these sets.....

Regards,

Gordon

[jonathandienst]

Christoph

I use the following approach to achieve the same effect as I think you are trying:

Expression 1 "Current/Selected year":

Sum({<ActivationYear={"$(=Year(Max(Total ActivationDate)))"}>} Amount)

Label 1:

=YearName(Max(Total ActivationDate))

Expression2 "Prior Year"

Sum({<ActivationYear={"$(=Year(Max(Total ActivationDate))-1)"}>} Amount)

Label 2:

=YearName(Max(Total ActivationDate), -1)

Dimension: ActivationMonth

There are ActivationMonth and ActivationYear list boxes on the sheet. If there are no selections, expression 1 applies to the current year, and expression 2 applies to the previous year. If the users selects a year (say 2007), then expression 1 will apply to 2007 and expression 2 to 2006.

Just change ActivationDate, ActivationYear, Amount to the correct fields for you model.

Hope that helps

Jonathan

[#9]

Thats the theory, but I do not get the whole formula to work 😞

=sum ( { $< Fakturadat={">=$(=addyears(yearstart(MaxOrderDate),-1)) <=$(=addyears(MaxOrderDate,-1))"}, Costumertype={Third}, InvoiceYear={"$(=year(MaxOrderDate)-1"}>} SalesAmount)

I changed to the $ instead of 1, but the year-part of the formula make probs (from "Invoice Year=" on )

I do not know where the mistake is, is there any digit missing oder an logic error in my formula?

Could you please adjust my year-part in the formula above?

I also labeled CY and PY (as Jonathan said) and then tried: year=PY or year={PY} , but QV does not accept it

Shame on me, hope you can give me one last push

Thank you both for your effort,

best regards,

christoph

[jonathandienst]

Christoph

I may be wrong, but it looks like one of your field names may be spelled incorrectly - should CostumerType not read CustomerType?

Jonathan