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]

May be like attached sample?

[#3]

Nice solution, but not good enough (as for my problem I think).

In my problem I have variables (values) on rows (as expressions) and dimensions (previous and current year) on columns. In your solution is inversely - values are as dimensions and years as expressions.

[#4]

If I use your solution I would have to do [2 * amount_of_my_variables] expressions. Then I also couldn't show them in two columns, because I would have to have every expression in one column (I see no other use).

[tresesco]

Thank you Monika, for giving us different scenario to work upon so that we can learn by the process. Could you post your sample qvw? Let us try to come up with a good-enough resolution.

[#6]

I should do such example as a first thing I added .qvw file to my question.

[pratap6699]

expressions:

Sum( {< Year = {"=$(=Max(year) )"}  >} Amount ) Lable: current year sales

Sum( {< Year = {"=$(=Max(year)-1 )"}  >} Amount ) Lable: previous year sales

[Gysbert_Wassenaar]

See attached example. Does that do what you want?

[#9]

Not exactly. I can't change the showing value of dimension (as I said earlier, I want Previous year and Current year instead of years in dimension). The value gain is also bad calculating (it should be income - cost, but it is not) and when I change it to this:

SUM({<Year={$(=max(Year)-1),$(=max(Year))}>}Income)-SUM({<Year={$(=max(Year)-1),$(=max(Year))}>}Cost)

The problem with disappearing years returned again.

[Gysbert_Wassenaar]

I'm afraid I don't understand what you want. Please post an excel file with the result table you want as end result.

[#11]

I used before:

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

as Dimension to get proper name in column. You used just "Year" in your example.

The result table which I want is in attached excel.