topic YTD and MTD Expression in QlikView

YTD and MTD Expression

abhaysingh — Sat, 07 Jun 2014 07:27:24 GMT

I want to show YTD and MTD , value of sales. please help me in the expression

Re: YTD and MTD Expression

Sat, 07 Jun 2014 07:37:39 GMT

Hi Abhay,

Its always better to use YTD & MTD in your master calendar.
In the expression level, you can use MonthStart() & YearStart() function in your expression.

--Lalit

Re: YTD and MTD Expression

its_anandrjs — Sat, 07 Jun 2014 07:44:41 GMT

For the expressions YTD and MTD you have a Datefield and then check the YTD and MTD expression with that but need to check you have latest dates in the database then write expression like below explanation. Also take any date dimensions for the charts.

YTD Expression

=Sum({$<Datefield = {'>$(=Max((YearStart(Today())))) <=$(=Max(Today()))'}>} Sales)

=Sum({$<Datefield = {'>$(=Max((YearStart(Datefield)))) <=$(=Max(Today()))'}>} Sales)

MTD Expression

=Sum({$<Datefield = {'>=$(=Max((MonthStart(Today())))) <=$(=Max(Today()))'}>} Sales)

=Sum({$<Datefield = {'>=$(=Max((MonthStart(Datefield)))) <=$(=Max(Today()))'}>} Sales)

Re: YTD and MTD Expression

abhaysingh — Sat, 07 Jun 2014 08:09:06 GMT

Hi lalit

thanks for ur input but could u pls elaborate your ans with some example.

Regards

abhay

Re: YTD and MTD Expression

MK_QSL — Sat, 07 Jun 2014 08:14:30 GMT

Rather than using Set Analysis, you can do the same by creating Flags in the script itself...

===============

Let vCompareDate = Today();

Sales:

Load * Inline

[

Date, Sales

01/01/2014, 100

01/02/2014, 120

01/03/2014, 1200

05/04/2014, 180

10/05/2014, 1000

01/06/2014, 150

05/06/2014, 1200

];

Load

Date,

If(InMonthToDate(Date, '$(vCompareDate)',0),'MTD') as Flag

Resident Sales;

Concatenate

Load Date,

If(InYearToDate(Date,'$(vCompareDate)',0),'YTD') as Flag

Resident Sales;

=====================

Now create a list box for Flag

and

use the same for you selection...

Or you can use something like below

=SUM({<Flag = {'YTD'}>}Sales)

=SUM({<Flag = {'MTD'}>}Sales)

REFERENCE :

topic YTD and MTD Expression in QlikView

YTD and MTD Expression

Re: YTD and MTD Expression

Re: YTD and MTD Expression

Re: YTD and MTD Expression

Re: YTD and MTD Expression

Valerio Fatatis