topic Re: YTD Calculation in QlikView

YTD Calculation

tyagishaila — Thu, 11 Dec 2014 10:27:39 GMT

I want year wise slots,

day and month must be Today's date but Year should not be

e.g.

YTD(11-12) is 1/1/2011 to 11/12/2012

YTD(12-13) is 1/1/2012 to 11/12/2013

YTD(13-14) is 1/1/2031 to 11/12/2014

This is for sale comparison, if I am comparing my sale today, than last year sale must be compare from start date to till date on last year

otherwise comparison will be wrong.

I think You understand my requirement

Re: YTD Calculation

Thu, 11 Dec 2014 10:51:14 GMT

Hi Shalia,

I've done YTDs like this in the past, I usually have 2 or 3 columns (one per Year for comparison). The below does this year and last year, for any extra years replace -1 in the second statement with -2 etc for 2012 etc....

Hope this helps

CY: sum({$<Year = {$(=max(Year))}, Month = {"<=$(=max({<Year={$(=max(Year))}>} Month))"}>} Sales)

LY =sum({$<Year = {$(=max(Year-1))}, Month = {"<=$(=max({<Year={$(=max(Year))}>} Month))"}>} Sales)

Re: YTD Calculation

tyagishaila — Fri, 12 Dec 2014 05:05:34 GMT

Hi Salvatore,

Thanks for reply, these CY and LY expressions are also helpful.

But its not full fill my requirement, There is no expression for today's date, like 12/12

its important, because we can compare sale of this year to sale of last year to particular date,

we don't have no data of this year after today's date, and we cant compare this year data till 12/12/14 to last year data till 31/12/13.

Re: YTD Calculation

tyagishaila — Fri, 12 Dec 2014 05:06:04 GMT

Hi Salvatore,

Thanks for reply, these CY and LY expressions are also helpful.

But its not full fill my requirement, There is no expression for today's date, like 12/12

its important, because we can compare sale of this year to sale of last year to particular date,

we don't have no data of this year after today's date, and we cant compare this year data till 12/12/14 to last year data till 31/12/13.

Re: YTD Calculation

tyagishaila — Fri, 12 Dec 2014 05:19:19 GMT

We can do it by using number of days,

as 1 year has total 365 days, than on particular date which day is there,

Like on 28th'Dec there are 362th day of year, we can take till 362th day of last year.

Re: YTD Calculation

Fri, 12 Dec 2014 09:33:01 GMT

Ah okay, will look into that today for you and come back, cheers

Re: YTD Calculation

tyagishaila — Fri, 12 Dec 2014 10:08:43 GMT

ThankYou so much Salvatore,

Re: YTD Calculation

Fri, 12 Dec 2014 10:57:09 GMT

Hi Shalia

I think this might give you want you seek (I hope) its basically at a day/date level instead.

Sum({<YourDate = {'$(=Date(Today()))', '$(=Date(AddYears(Today(), -1)))'} >} SalesAmount)

Re: YTD Calculation

tyagishaila — Fri, 12 Dec 2014 11:05:46 GMT

Really very very thanks to you,

As I am not sure because I am not well aware to qlikview.

I think,It takes today's date and same date of last year because of -1.

(Its perfect).

To create same slots of 1 year back, i.e. -2,

I have to do,

Date(Today()))' = Date(AddYears(Today(), -1)

and '$(=Date(AddYears(Today(), -1) = $(=Date(AddYears(Today(), -2) and so

on..

Is am I right or not?

On Fri, Dec 12, 2014 at 4:27 PM, Salvatore Zannino <qcwebmaster@qlikview.com

Re: YTD Calculation

Fri, 12 Dec 2014 11:12:24 GMT

yes you are correct - you've got it!

Re: YTD Calculation

tyagishaila — Fri, 12 Dec 2014 11:14:10 GMT

Okay,

ThankYou

On Fri, Dec 12, 2014 at 4:42 PM, Salvatore Zannino <qcwebmaster@qlikview.com

Re: YTD Calculation

Fri, 12 Dec 2014 11:15:13 GMT

Cool 🙂 if it works please mark my answer as correct

Re: YTD Calculation

tyagishaila — Fri, 12 Dec 2014 11:18:58 GMT

Sure

On Fri, Dec 12, 2014 at 4:45 PM, Salvatore Zannino <qcwebmaster@qlikview.com

Re: YTD Calculation

Fri, 12 Dec 2014 11:29:59 GMT

Thanks Shaila - have a nice day