topic Re: Re: Calculating differences based on row values in QlikView

Calculating differences based on row values

Fri, 18 Apr 2014 03:52:05 GMT

Hi folks,

Please view the image below before hand:

I would like to know how I could calculate the difference based on the values of the rows. What I mean here is, for e.g. IF I wanted to calculate the difference between the no. of users for 2/2014 and 1/2014, how could i do so? Derive the value of the current month minus its previous month.

Thanks in advance.

Best Regards,

Re: Calculating differences based on row values

tresB — Fri, 18 Apr 2014 05:11:37 GMT

Try with above()/below() functions. If any doubt, try to share a sample qvw and explain expected output there.

Re: Calculating differences based on row values

Fri, 18 Apr 2014 05:35:38 GMT

Hi tres,

Will try it out and get back to you on that. Thanks.

Re: Re: Calculating differences based on row values

Fri, 18 Apr 2014 06:06:26 GMT

Hi tres,

Attached herewith is a sample .qvw.

Please do have a look at it, I have placed a comment in the script to explain what is my desired results

Cheers

Re: Re: Re: Calculating differences based on row values

Fri, 18 Apr 2014 06:16:32 GMT

Hi Ram,

PFA....

Regards

Ankur

Re: Re: Re: Calculating differences based on row values

tresB — Fri, 18 Apr 2014 06:27:43 GMT

May be like attached sample

Re: Calculating differences based on row values

MK_QSL — Fri, 18 Apr 2014 06:34:39 GMT

Check enclosed file...

Re: Re: Calculating differences based on row values

Fri, 18 Apr 2014 06:59:37 GMT

Thanks tres!

Re: Calculating differences based on row values

Fri, 18 Apr 2014 07:01:21 GMT

Hi Ram,

You can Use this script to derive difference between no. of users for current month minus Previous month.

Source :

LOAD * INLINE [

Year, Quarter, MonthOfYear, UniqueChargedUsers

2012, 2, 5, 22030

2013, 4, 12, 58521

2014, 1, 1, 72378

2014, 1, 2, 52969

];

NoConcatenate

Source1:

Load *

Resident Source

Order by Year, MonthOfYear;

Drop Table Source;

Source2 :

Load

Year,

MonthOfYear,

UniqueChargedUsers,

if((Year = Peek(Year) and MonthOfYear = Peek(MonthOfYear) + 1 ) or(Year = Peek(Year) + 1 and MonthOfYear = 1 and Peek(MonthOfYear) = 12 ),UniqueChargedUsers-Peek(UniqueChargedUsers)) as DiffrenceOfCurrentAndPreviousMonth

Resident Source1;

Drop Table Source1;

Result will be like this.

Regards,

Vivek