Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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

Tags (2)
15 Replies
Not applicable

Finding YTD Values

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

Finding YTD Values

Hi,

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

Not applicable

Finding YTD Values

Hi,

Can You attached a sample file .

Not applicable

Finding YTD Values

Hi Rahul/Jai,

Can i have the sample application too

Thanks

Sunil

Not applicable

Finding YTD Values

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
Contributor II

Finding YTD Values

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
Contributor II

Finding YTD Values

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

Not applicable

Finding YTD Values

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

Finding YTD Values

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



Community Browser