topic Re: MTD Sales ? in QlikView

MTD Sales ?

Thu, 23 Jul 2015 17:03:52 GMT

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

Re: MTD Sales ?

MK_QSL — Thu, 23 Jul 2015 19:41:04 GMT

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)

Re: MTD Sales ?

Fri, 24 Jul 2015 03:52:28 GMT

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

Re: MTD Sales ?

prashantbaste — Fri, 24 Jul 2015 04:45:40 GMT

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

Re: MTD Sales ?

prashantbaste — Fri, 24 Jul 2015 04:58:06 GMT

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

Re: MTD Sales ?

Fri, 24 Jul 2015 05:27:43 GMT

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)