topic Re: Set dimension columns as the current and the previous year in QlikView

Set dimension columns as the current and the previous year

Mon, 28 Sep 2015 06:52:54 GMT

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

Re: Set dimension columns as the current and the previous year

Gysbert_Wassenaar — Mon, 28 Sep 2015 06:59:42 GMT

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

Re: Set dimension columns as the current and the previous year

SreeniJD — Mon, 28 Sep 2015 07:04:30 GMT

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

Re: Set dimension columns as the current and the previous year

Mon, 28 Sep 2015 07:14:54 GMT

Yeah, that's what I am saying.

Re: Set dimension columns as the current and the previous year

Mon, 28 Sep 2015 07:16:36 GMT

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.

Re: Set dimension columns as the current and the previous year

SreeniJD — Mon, 28 Sep 2015 07:24:39 GMT

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

Re: Set dimension columns as the current and the previous year

Mon, 28 Sep 2015 07:34:46 GMT

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?

Re: Set dimension columns as the current and the previous year

Digvijay_Singh — Mon, 28 Sep 2015 07:49:16 GMT

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

Re: Set dimension columns as the current and the previous year

Mon, 28 Sep 2015 08:13:18 GMT

I've edited my question.

Re: Set dimension columns as the current and the previous year

teiswamsler — Mon, 28 Sep 2015 08:27:53 GMT

Hi Monika

Try use these expressions in chart

CurrentYear:

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

PreviousYear:

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

/Teis

Re: Set dimension columns as the current and the previous year

tresB — Mon, 28 Sep 2015 08:29:22 GMT

May be like attached sample?

Re: Set dimension columns as the current and the previous year

Mon, 28 Sep 2015 08:44:47 GMT

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.

Re: Set dimension columns as the current and the previous year

Mon, 28 Sep 2015 08:50:22 GMT

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).

Re: Set dimension columns as the current and the previous year

tresB — Mon, 28 Sep 2015 08:57:43 GMT

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.

Re: Set dimension columns as the current and the previous year

Mon, 28 Sep 2015 09:20:35 GMT

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

Re: Set dimension columns as the current and the previous year

pratap6699 — Mon, 28 Sep 2015 09:23:14 GMT

expressions:

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

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

Re: Set dimension columns as the current and the previous year

Gysbert_Wassenaar — Mon, 28 Sep 2015 09:35:39 GMT

See attached example. Does that do what you want?

Re: Set dimension columns as the current and the previous year

Thu, 01 Oct 2015 06:01:42 GMT

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.

Re: Set dimension columns as the current and the previous year

Gysbert_Wassenaar — Thu, 01 Oct 2015 06:06:49 GMT

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

Re: Set dimension columns as the current and the previous year

Thu, 01 Oct 2015 06:31:30 GMT

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.