Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi you all.
I have built a pivot table with three dimensions: Product, Year, Month. I'd like to get a table with one row for each product, and one column for each year-month combination.
I need to write an expression returning the previous year sales (for every month). That is, I need to perform my calculations as if Year field wasn't present in the table.
If I try something as simple as this: SUM({$<Year= {2011}>} Sales), I just get a valid data in the 2011 context.
For example (look at that 1500: it's the previous year sales for January 2012):
2011 | 2011 | 2011 | 2011 | 2012 | 2012 | 2012 | 2012 | |
Product | Jan | Jan | Feb | Feb | Jan | Jan | Feb | Feb |
---|---|---|---|---|---|---|---|---|
Prev. Year | Current Year | Prev. | Current | Prev. | Current | Prev. | Current | |
A | - | 1500 | - | 2000 | 1500 | 4000 | 2000 | 7515 |
B | - | 715 | - | 1555 | 715 | 7017 | 1555 | 650 |
How can I ask Qlikview to forget he's under the influence of the current year so he can give me the previous year sales in that orange cells...
Thanks in advance!
Ok, try
=aggr(above(sum({<Year=>} Value)), Product, Month, Year)
edit:
attached sample file
Try something like
=aggr(above(sum(Value)), Product, Month, Year)
as expression for Prev (The order of dimensions is important here!)
Regards
Stefan
edit: attached sample file
Thank you very much Stefan, you have showed me an interesting usage of the Above function that I didn't know. This will be very helpful for me in this case and in future ones.
But... (there's always a "but"...), let's suppose you add a listbox for the Year field, in your example. Then, select 2012 and 2013, and disable the "Current" expression (I just want to show one expression, performing an operation with current and previous year). Now, you just get data for 2013. I think that happens because 2011 is not selected so there is no previous year for 2012. Is there any thing we can do to solve it?
Thank you again!
This is taken straight from the F1 help on Set Analysis - give it a try:
sum( {$<Year = {$(#=Only(Year)-1)}>} Sales )
Hope this helps,
Jason
Thanks for your quick reply, Jason.
I've tried your expression in the swuehl example (previous answer to yours) and it doesn't work. Is it maybe because I can't go the Only way in a pivot table? Is the Above function the only way to get the previous year in a pivot table(having, obviously, the Year field as a dimension in the table; if Year wasn't there, there wouldn't be a problem)?
So far, I'm able to get the previous year (with Above function), but if I have 2011-2012-2013, I need to make a selection of all three years to work properly. I don't want to be forced to select 2011.
Come on guys, we're close!
Ok, try
=aggr(above(sum({<Year=>} Value)), Product, Month, Year)
edit:
attached sample file
Hi,
use expression like this.Its help for u.
=Sum({<Year=, NumDate={'>=$(=(Num(YearStart(AddYears((selectedDate),-1)))))<=$(=(Num(AddYears((selectedDate),-1))))'}>}Sales)
Where Num(Datefield) AS NumDate
selectedDate=MakeDate(Max(Year),Max(Month),Max(Day))
Regards,
Iyyappan.
Fine, writing "Year=" has solved that point. Now I can get data from previous year even if it isn't selected in my listbox.
I have added an "If" with Dimensionality and SecondaryDimensionality functions to make calculations at different levels depending on the cell I am (I have several partial sums).
It looks like everything it's working great.
Thank you all!