Blank columns when using VRank

[ramonarcusa36]

Hi guys.

I'm stuck with VRank function.

I've built a pivot table with one dimension in x-axis and two dimensions in y-axis. One of them is the Year field. I attach an example for better comprehension.

In the body of my table, I need to show a Vertical Rank. My Familias will be ranked (in every year/segmento) based on Kilogrames they got the previous year, for that Segmento. (Note: the real situation will be about growth, not just the previous year kilogrames; I'm just simplifying to make it clearer for you).

As you can see, I have selected years 2012 and 2013. My main problem is that I don't know why 2011 stays there. I just need the years I've selected to stay.

What surprises me the most is that the problem is originated by the VRank function. To check it, you can disable the currently enabled expression (VRank), and enable the other one (the one that calculates previous years kilogrames), and 2011 will go away. It looks like VRank needs 2011 to stay. Why?

Do you think of any other way to do this? I know I could try getting previous year kilogrames with Above function, but then I'd find other problems, for example, I'd just get data for those years whose previous one is visible. And Above function would force me to modify script to get data in a particular order (order by year) and I wouldn't like to depend on that.

Any help?

Thank you!!

[swuehl · Accepted solution]

The reason why you see the year 2011 with vrank enabled is, that vrank function will disable 'supress zero values' (like described in the Help). You can see the same effect with your second expression by deactivating this option in presentation tab.

But maybe like attached?

[ramonarcusa36]

Yes, you're right, I misread the help, I didn't realise that VRank disables the "supress zero values" property. It looks like there's nothing to do with that.

I tried your point, with aggr function, and it sounds good, but when I try to bring that to my actual solution, QV makes weird things, changes formats (colors, fonts...) and other stuff. So I think we'll just keep those empty columns for 2011. It's not critical here.

Thank you very much!