Set dimension columns as the current and the previous year

[#1]

Hi!

I have such problem. I want to show in Straight Table on rows values such as amount, income and some other variables. On columns I want to have year = vMaxYear as CurrentYear and year=vPriorYear as PreviousYear, where vMaxYear is the max available year and vPriorYear as vMaxYear-1.

Can I simple do this?

For now I have conditional in column expression:

=if([Year]=$(vMaxYear),'Current year',if([Year]=$(vPriorYear),'Previous year',Null))

and it show only two columns when these two years are in data. When there is only one (like now, 2015), it shows only one column.

=====

Edit:

What I need? -> I would like always to show data for current year and previous year, even the previous year is not present in data.

*Furthermore this conditional doesn't work if I choose any year in filter (there will be only one column).

===

Edit2:

When I have chosen 2015 year (in data I have 2014 and 2015), it should looks like:

|            Previous year  |  Current year

amount          30                        40

income          40                        50

costs            35                        48

gain              5                          2

*Gain is for me income-costs.

Now it is only second column. When I choose 2014, there should be first column with zeros and second like this first above (but now it is only one - with data from 2014 in Current year column).

PS. sorry for my grammar

===

Edit3:

Finally I add some example in .qvw

[tresesco · Accepted solution]

PFA. Hope this is what you want

[#3]

Yeah! It is really good!
Can you tell me one more thing, please?

Is there any possibility to hide the lowest year value (in this case 2013) in List Box "Year" or "Date"?

==

ok, I get it too. I add variable: "let vMinYear  = '=min({<Date=,Year=>}Year)';" and I add in List Box Field Expression: "=IF(Year<>vMinYear,Year)".

Thanks for help!

[#4]

I wanted do the same table as you but I couldn't. If I copy your chart table, it works. What options did you used (except add new dimension and expressions)?

What is also strange - when I created new qvw and add new chart table like yours - it worked...

---

edited:

I see now that ever your table doesn't work in my sheet, when I remove fields which have zero values. In yours qvw everything works. I find out also that my script is the reason why this is not work, but I don't know why. I will ask another question on Community.

[#5]

Here is my next question:

Column with data from previous year disappear

If you want, you can look at it.