topic Re: MTD and YTD in QlikView

MTD and YTD

Tue, 02 Oct 2012 14:28:36 GMT

Anybody could please help me to find out the formulas for MTD and YTD, without selecting Year and Month.

I have done it with selecting Year and Month.

Thanks in Advance.

Re: MTD and YTD

Anonymous — Tue, 02 Oct 2012 15:18:14 GMT

Hi Renjith,

The easiest way I think is to create a calender with a Year and a YearMonth field in your script.

Try This:

Load ...

Year(DATE) AS Year,

date(monthstart(DATE), 'YYYY-MM') AS YearMonth,

From ...

Then:

Sum ({$<Year={$(=Max(Year))}, DATE={'<=$(=Today())'} >} SALES)

Gives you the SUM(SALES) of the max(Year) form your data (up to today)

And

Sum ({$<YearMonth={$(=Max(YearMonth))}, DATE={'<=$(=Today())'} >} SALES)

Gives you the SUM(SALES) of the max(YearMonth) form your data (up to today)

Re: MTD and YTD

kamalqlik — Tue, 02 Oct 2012 15:42:43 GMT

Hi Renjith,

This can be done in many ways...

In case you want static result----

1)Use inyeartodate(D, $(vToday), 0) * -1 AS CYTD in back end(vToday=LET vToday = vMaxDate;).

ansd in expression in FrontEnd use-----
sum(if(CYTD, LineSalesAmount)).

For LYTD--
sum(if(FPYTD, LineSalesAmount)) where FPYTD=inyeartodate(D, $(vToday), -1) * -1 AS FPYTD in Script

For MTD---sum(if(CMTD, LineSalesAmount))

Where CMTD=inmonthtodate(D, $(vToday), 0) * -1 AS CMTD in script

For Previous month this year sum(if(PM, LineSalesAmount))......

where PM =inmonth(D, $(vToday), -1) * -1 AS PM in script

For Dyanamic result you have to go for the Selection....or you can go as Dennis have recommended but you have to bypass the Year and Month Field.

=num(sum({<[Fiscal Month]=, [Fiscal Quarter]=,[Fiscal Year]={$(=max([Fiscal Year]))},[ Date]={"<=$(vCurrDate)"}>}[Sales])) .

Hope it help.I have also bypass Quarter.

Regards

Kamal

Re: MTD and YTD

Tue, 02 Oct 2012 16:54:35 GMT

Dennis,

the first was working fine.

but the second (Sum ({$<YearMonth={$(=Max(YearMonth))}, DATE={'<=$(=Today())'} >} SALES))

returns a null value.

Please have a look on that.

Re: MTD and YTD

kamalqlik — Wed, 03 Oct 2012 07:49:09 GMT

Hi ,

It is working wrong because max(yeaMonth) is returning the null value in your case.

This is because you must be using....

(Month(Date)&'-'& right(Year(Date),2)) as YearMonth for calculating YearMonth or some other method for calculating YearMonth.

This is text field and when you are using max(YearMonth) it will return null value.

So you have to change it to a number format.

So use Like this to make yearmonth

The field that you have made Yearmonth,

Make a resident table with all the field

and use

Date(Date(Date#(YearMonth,'MMM-YY'),'DD-MM-YYYY'),'MMM-YY') as YearMonth1,

resident your table.

To make the YearMonth Number.

and use this YearMonth1 in your expression.

Hope it helps.

Regard

KAmal