Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Finding YTD Values

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

15 Replies
Not applicable
Author

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

Not applicable
Author

Hi,

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

Not applicable
Author

Hi,

Can You attached a sample file .

Not applicable
Author

Hi Rahul/Jai,

Can i have the sample application too

Thanks

Sunil

Not applicable
Author

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

gajapathy74
Creator II
Creator II

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,

gajapathy74
Creator II
Creator II

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

Not applicable
Author

Hi,

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

Thanks & Regards

Jai

Not applicable
Author

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