Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi,
Thanks for your kind reply.When I tried it not working properly.So could you show the sample code/qvw
Hi,
Can You attached a sample file .
Hi Rahul/Jai,
Can i have the sample application too
Thanks
Sunil
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
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,
I am sorry, by mistake i clicked the Suggest as Answer button instead of Reply.
Hi,
Thank you,Thing is it show the value for user based selection only.I need cumulative values of months.
Thanks & Regards
Jai
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