MTD date issue ?

[#1]

HI All,

There is no relation between these tables, but in the all the tables Used [Invoice Date] ...

I have created top customers report they are working fine on Invoice date, but i have created MTD using YrRollMonth with the Varible as vMthDt in Gross Margin (MTD Logic)..

They are Separately working fine with their filters, but I need to use Invoice date & Year as Filters, Is this possible can any one help me...

AllInvoiceDate:

LOAD DISTINCT

  [Invoice Date],

  year([Invoice Date]) as [Invoice Year],

  month([Invoice Date]) as [Invoice Month]

RESIDENT Invoices;

Current_Rollmonth:

Load

MonthName([Invoice Date]) As YrRollMonth

Resident Invoices

Where Year([Invoice Date]) = Year('$(vMaxDt)');

MaxInvoiceDt:

Load

  max([Invoice Date]) As maxInvoiceDt

Resident Invoices;

Let vMaxDt = Date(Peek('maxInvoiceDt'));

Variable:   vMthDt= Date(YrRollMonth)

[morgankejerhag]

Hi,

I am not sure what you are trying to achieve. Is it a sum of month to date? In this case you can create a flag for each transaction or in a calendar (AllInvoiceDate in your example).

In the script:

if(InMonthToDate([Invoice Date].today(),0),1,0) as Flag_MTD

Then in your expression in the chart you can use:

sum({<Flag_MTD={1}>} Value)

If you want to have a MTD value for any date in the history you should look into AsOf-calendar functions.

Best regards,

Morgan Kejerhag

Senior Business Intelligence Consultant, Drake Analytics

http://www.drakeanalytics.se