MTD Sales ?

[#1]

HI All,

I Need to find MTD Sales for Current Month Till Date Like July 23..

So I need to See  12 Running Months in in My Report, But I need to See Each & Evry Month also Like ..

Example: Jul 1-July 23

               Jun 1-Jun 23

               May 1-May 23

& So On...Each & Every Month Needs to take as Todays Date in their Respective Months...

Kindly Find the attachment Excel & also Share Some example Urgent....

Regards,

Helen

[MK_QSL]

Script

Sales:

Load

  Products,

  Date,

  Month(Date) as Month,

  Year(Date) as Year,

  Day(Date) as Day,

  Sales

From TableName;

Now Create a Pivot Table

Dimension

Products

Month

Expression

SUM({<Day = {">=1<=$(=Day(Today()))"}>}Sales)

[#3]

HI Manish,

Thanks , But I need to See Last 12 Months Based on each & every Selection Can you help me on that..

Share with Some Test example...

Regards,

Helen

[prashantbaste]

Hi

You can either use 12 variables for 12 months or write 12 expressions as -

For current month :  From: MonthStart(Date(Today()))  To Date(Today())

For last month :  From :  MonthStart(AddMonths(Date(Today()),-1))  To  AddMonths(Date(Today()),-1)

For 2nd last month :  From :  MonthStart(AddMonths(Date(Today()),-2))  To  AddMonths(Date(Today()),-2)

and so on ... upto

For last 12th month :  From :  MonthStart(AddMonths(Date(Today()),-12))  To  AddMonths(Date(Today()),-12)

And then,

Sum({<DateFieldName= {>=MonthStart(Date(Today()))  <=Date(Today()) } >} Amount)

Hope this will be helpful for you.

--

Regards,

Prashant P Baste

[prashantbaste]

Hey

The solution given by Manish is also be useful just you need to make some changes in dimension as -

Dimension 1 as Product

Dimension 2 as calculated dimension as - If(Datefieldname >= AddMonths(DateFieldName,-12), Date(DateFieldName,'MMM YYYY')

rest everything will remain same.

Hope this will be helpful for you.

--

Regards,

Prashant P Baste

[#6]

Sales:

Load

  Products,

  Date,

  Month(Date) as Month,

  Year(Date) as Year,

  Day(Date) as Day,

Date(monthstart(Date), 'MMM-YYYY') as MonthYear,

  Sales

From TableName;

Expression for MTD:

= sum({$<Year = {'$(=Year(AddMonths(Max(Date),-1)))'}, Month = {'$(= Month(AddMonths(max(Date),-1)))'},Day = {'<=$(= Day(Max(Date)))'},Date= ,MonthYear =>}Amount)