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

[Gysbert_Wassenaar]

I don't understand what you're trying to do. Are you trying to display information that does not exist?

[SreeniJD]

Hi Monika,

If I understand your concern partially..

You would like to show data for current year and previous year..

If data is not present in previous year, you still want to show the column as static..

can you please clarify

HTH

Sreeni

[#4]

Yeah, that's what I am saying.

[#5]

Like jaaldurgam‌ wrote: I would like always to show data for current year and previous year, even the previous year is not present in data.

[SreeniJD]

Hi Monika,

there is no direct workaround here... but with set analysis we can get the desired result.

Create a fact for previous year sales and lets say have an expression like (Sum{1} PreviousYear) and hide this so even though you don't have data for the previous year, you can still get the column visible.

HTH,

Sreeni

[#7]

Thanks for answer.

Have you read, what I added to question? I wrote: "this conditional doesn't work if I choose any year in filter". Will your solution also fix this problem?

I'm not quite sure where should I do these changes which you describe. I should create fact as a variable and then I should add expression in my table?

[Digvijay_Singh]

Can you share output format having 3-4 sample rows of data?

[#9]

I've edited my question.

[teiswamsler]

Hi Monika

Try use these expressions in chart

CurrentYear:

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

PreviousYear:

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

/Teis