topic Finding YTD Values in QlikView

Finding YTD Values

Tue, 23 Feb 2010 11:18:08 GMT

Dear All,

I am having a scenario,that have to calulate YTD&MTD values in pivot table.

Ytd is nothing but cummltive values.And MTD values values belongs to current month.Iam having data

from Apr 2009 to Sep 2009.Thing is when I clik Jul 2009,in YTD I have to attain cummltive values of APr 2009,May 2009,Jun 2009 and Jul 2009.That means,

YTD of Jul 2009->(Apr 2009+May 2009+Jun 2009+Jul 2009)

So if any one of you come across this kind of issue please help me out.It is very urgent.

Thanks&Regards

Jai

Finding YTD Values

Tue, 23 Feb 2010 11:48:16 GMT

Hi Jai,

Use Common Calender for selection and Use Variables for reduce data in the expression .

For MTD expression use inmonthtodate() Function

For YTD expression use inyeartodate () Function

Hope you will get the correct path to proceed further by using these functions

Thank You

Rahul

Finding YTD Values

Tue, 23 Feb 2010 12:07:43 GMT

Hi,

Thanks for your kind reply.When I tried it not working properly.So could you show the sample code/qvw

Finding YTD Values

Tue, 23 Feb 2010 12:19:41 GMT

Hi,

Can You attached a sample file .

Finding YTD Values

Tue, 23 Feb 2010 12:28:40 GMT

Hi Rahul/Jai,

Can i have the sample application too

Thanks

Sunil

Finding YTD Values

Tue, 23 Feb 2010 12:29:45 GMT

Hi,

Here with I have attached sample xls file.Thing is when user click particular month,it should show cumulative value of all the months.

Thanks & Regards

Jai

Finding YTD Values

gajapathy74 — Tue, 23 Feb 2010 12:46:26 GMT

Hi,

Assuming you have two different set of fields for Year and Month selection and then the following expression would get you an answer.

Sum({<Year={'=$(=Max(Year))'}, Month={'<=$(=Max(Month))'}>} FieldNameToBeCalculated)

Regards,

Finding YTD Values

gajapathy74 — Tue, 23 Feb 2010 12:50:24 GMT

I am sorry, by mistake i clicked the Suggest as Answer button instead of Reply.

Finding YTD Values

Tue, 23 Feb 2010 12:51:18 GMT

Hi,

Thank you,Thing is it show the value for user based selection only.I need cumulative values of months.

Thanks & Regards

Jai

Finding YTD Values

Tue, 23 Feb 2010 12:52:51 GMT

Hi ,

You have to convert your date Field from XLS to text

and use makedate function to convert it into date format

Then you can calculate The MTD ,YTD

e.g may be script like this

A:
LOAD Year,
Date,
Sales
FROM

(biff, embedded labels, table is Sheet1$);

B:
Load
text(Date) as Date1,
left(text(Date),2) as day,
mid(text(Date),4,2) as Month,
Right(text(Date),4) as Year,
makeDate(Right(text(Date),4),mid(text(Date),4,2),left(text(Date),2)) as Dt,
Month(makeDate(Right(text(Date),4),mid(text(Date),4,2),left(text(Date),2))) as Month_Name,
Sales,
1 as junk
Resident A;
Drop table A;

and in the chart

MTD expression Sum(Sales) according to Month Dimension,

YTD expression -- lable of MTD expression and make it Full Accumulation checked

Hope this will help you

Thanks

Rahul

Finding YTD Values

gajapathy74 — Tue, 23 Feb 2010 14:31:47 GMT

Hi,

Please check the attached QlikView file.

Sum({$<SalesMonth={'<=$(=Max(SalesMonth))'}>}SalesAmount)

Regards,

Finding YTD Values

Wed, 24 Feb 2010 06:07:52 GMT

Hi Dear Friend,

Thank you very much for your effort..Its working nicely.But thing is when the year completed,we click January for next year it showing that particular months value only.For example when I clicked January 2008 it showing Jan-08 value only instead of cumulative value of Jan07-Dec07 in YTD values.So let me know that where can I change the script.It will great helpful for me.

Thanks & Regards

Jai

Finding YTD Values

Wed, 24 Feb 2010 07:30:48 GMT

Hi,

is your requirement is Financial year ?

Finding YTD Values

Wed, 24 Feb 2010 07:40:41 GMT

Hi,

ya it should be Financial Year.Financial Year staring at Apr

Note :

If it is continuous process whether irrespective of year selection,how should I change

Thanks & Regards

Jai

Finding YTD Values

Tue, 09 Nov 2010 15:26:48 GMT

Okay,

I have tried all the options and I had a few successes with this, but unfortunately I can't find the answer to my problem.

I am trying to make a chart with YTD (In PIVOT table layout). However, the functions do not work properly.

See attached example..

Please help, I am trying this for days already..

Kind regards,

Wouter

Finding YTD Values

Tue, 09 Nov 2010 16:22:23 GMT

Answer =

Try the following function in your pivot table

rangesum(before(sum(OMZET),0,12))

Set analysis doesn't work when the set anaylsis is done over fields that are defined as dimensions.