topic Please help with the last year values in QlikView

Please help with the last year values

Sat, 06 Sep 2014 08:50:59 GMT

Hello everyone,

Please help into this, i have one single table, i am getting Last Year values from the same table by using key ( i created the key based on the year and other objects)

Can you please look into the image for the issue.

If you look the value for the 2011 the last year value (2010) is coming properly, but if see 2012 value, the last year value (2011) is not coming properly,

Can any body tell what is the issue.

Thanks in advance

Re: Please help with the last year values

ecolomer — Sat, 06 Sep 2014 09:13:38 GMT

You can use ABOVE function or BEFORE function

Re: Please help with the last year values

Sat, 06 Sep 2014 09:18:14 GMT

Thanks for your response Enrique,

Can you please tell me where to use, since i am very new to qlikview.

Thanks

Re: Please help with the last year values

PrashantSangle — Sat, 06 Sep 2014 09:18:20 GMT

Hi,

What expression you are using to calculate last Year Values??

Regards,

Re: Re: Please help with the last year values

ecolomer — Sat, 06 Sep 2014 09:27:56 GMT

An simple example

Re: Re: Please help with the last year values

IAMDV — Sat, 06 Sep 2014 09:31:33 GMT

Hi,

It's difficult to help without looking at the object/expression. I'd receommend you to post the sample QVW file and one of us will help you.

Cheers,

www.QlikShare.com

Re: Please help with the last year values

Sat, 06 Sep 2014 09:39:52 GMT

Hi Max, I am getting Last year data by using below code( i wrote at script level)

Table1:

LOAD

Year-1&'-'&Month as %Key1;

Load

DATE,

MonthNo,

Revenue

FROM

Table(qvd)

Left Join(Table1)

LOAD

Distinct

Year&'-'&Month as %Key1,

Revenue as LY_Revenue,

FROM

Table1.qvd

i am creating one more qvd , i am using that, when i place year, revenue, LY Revenue, it is having the issue, please look at the screen shot, If you look the value for the 2011 the last year value (2010) is coming properly, but if see 2012 value, the last year value (2011) is not coming properly,

Please help into that

Re: Please help with the last year values

Sat, 06 Sep 2014 09:40:43 GMT

Hi Deepak, I am getting Last year data by using below code( i wrote at script level)

Table1:

LOAD

Year-1&'-'&Month as %Key1;

Load

DATE,

MonthNo,

Revenue

FROM

Table(qvd)

Left Join(Table1)

LOAD

Distinct

Year&'-'&Month as %Key1,

Revenue as LY_Revenue,

FROM

Table1.qvd

i am creating one more qvd , i am using that, when i place year, revenue, LY_Revenue, it is having the issue, please look at the screen shot, If you look the value for the 2011 the last year value (2010) is coming properly, but if see 2012 value, the last year value (2011) is not coming properly,

Please help into that

Re: Please help with the last year values

Sat, 06 Sep 2014 09:42:14 GMT

Hi Enrique, I am getting Last year data by using below code( i wrote at script level), i think above and before function i can not use here

Table1:

LOAD

Year-1&'-'&Month as %Key1;

Load

DATE,

MonthNo,

Revenue

FROM

Table(qvd)

Left Join(Table1)

LOAD

Distinct

Year&'-'&Month as %Key1,

Revenue as LY_Revenue,

FROM

Table1.qvd

Please help into that

Re: Please help with the last year values

ecolomer — Sat, 06 Sep 2014 09:44:42 GMT

You can use:

Sum({<yourYear=,yourMonth=,YearField = {'$(=Max(YearField)-1)'}>} Sales)

Re: Please help with the last year values

PrashantSangle — Sat, 06 Sep 2014 09:52:58 GMT

Hi,

I will suggest you to do you calculation on front end.

Use Set analysis as Enrique Suggested.

Regards

Re: Please help with the last year values

Sat, 06 Sep 2014 10:25:35 GMT

Hello Enrique,

Why two year fields here?

Thanks

Re: Please help with the last year values

PrashantSangle — Sat, 06 Sep 2014 10:59:10 GMT

Hi,

He had given you general Syntax as

Write expression

For Current Year

Sum({<YearField={"$(=Max(YearField))"}>}Sales)

and

For Previous Year

Sum({<YearField={"$(=Max(YearField-1))"}>}Sales)

and Remove Other Field as per Requirement which Enrique try to suggest you.

Regards,