Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

satish25
New Contributor III

Sum of sales of previous year MTD

Hello All,

I want the Expression for the 'Previous Year Month to Date'

However if I select a particular month, it should show me the Value From the

         'Start of the Previous Year to the End of the Selected  Month'.

E.g :      This Year Being 2018, if i Select the Month May, it should show me the values .i.e Sum(Sales) from                    1st Jan 2017 to 31st May 2017.

          Date Field in the Data is 'Order_Date'.

1 Solution

Accepted Solutions
MVP
MVP

Re: Sum of sales of previous year MTD

Create Month and Year in the script, rather then creating them on the front end... when you used my expression, there was not field called Month and that is why it wasn't working....

I added a table so that you can see which date ranges are showing up...

All the best

Best,

Sunny

11 Replies
omarbensalem
Esteemed Contributor

Re: Sum of sales of previous year MTD

sum({< DATE=,Year=,Month=,Quarter=,DATE={">=$(=YearStart(addYears(max(DATE),-1)))<=$(=addYears(max(DATE),-1))"}>}YourMeasure)


and please refer to this thread in which I tried to explain this step by step:

YTD, MTD issue

MVP
MVP

Re: Sum of sales of previous year MTD

So when nothing is selected you want to see 12/01/2017 till 12/31/2017 (Previous MTD) and if May is selected then you would want to see 01/01/2017 till 05/31/2017? right?

Try this

{<Date = {"$(=If(GetSelectedCount(Month) = 0, '>=' & MonthStart(Today(), -1) & '<' & MonthStart(Today()), '>=' & YearStart(Max(Date)) & '<' & MonthStart(Max(Date), 1)))"}, Month, Year, YearMonth, Quarter, QuarterYear>}

satish25
New Contributor III

Re: Sum of sales of previous year MTD

Hi Sunny,

Sorry for the late response, was caught up in some other work

Actually I forgot to tell you that suppose I have a

Measure : Budget_Sales,

And I want to find the Sum(Budget_Sales), using the above logic, what changes would be needed in the equation ??

Sorry once again for the late reply

MVP
MVP

Re: Sum of sales of previous year MTD

There was not aggregation in the above set analysis I gave you... may be you want this

Sum({<Date = {"$(=If(GetSelectedCount(Month) = 0, '>=' & MonthStart(Today(), -1) & '<' & MonthStart(Today()), '>=' & YearStart(Max(Date)) & '<' & MonthStart(Max(Date), 1)))"}, Month, Year, YearMonth, Quarter, QuarterYear>} Budget_Sales)

satish25
New Contributor III

Re: Sum of sales of previous year MTD

Thanks a Lot Sunny.

On selecting a particular Date this works perfectly

But when no selections are made, the data is shown of the current year,

I want the data to be shown of the previous year

MVP
MVP

Re: Sum of sales of previous year MTD

So when nothing is selected, you would want to see Dec 1st 2017 to Dec 31st 2017? May be this

Sum({<Date = {"$(=If(GetSelectedCount(Month) = 0, '>=' & MonthStart(Today(), -13) & '<' & MonthStart(Today(), -12), '>=' & YearStart(Max(Date)) & '<' & MonthStart(Max(Date), 1)))"}, Month, Year, YearMonth, Quarter, QuarterYear>} Budget_Sales)

if you want Jan 1st 2017 till Dec 31st 2017 without selection, then try this

Sum({<Date = {"$(=If(GetSelectedCount(Month) = 0, '>=' & YearStart(Today(), -1) & '<' & YearStart(Today(), 0), '>=' & YearStart(Max(Date)) & '<' & MonthStart(Max(Date), 1)))"}, Month, Year, YearMonth, Quarter, QuarterYear>} Budget_Sales)

satish25
New Contributor III

Re: Sum of sales of previous year MTD

No it isn't working

And even on selecting it's showing the current month value, not from Jan to the selected month

MVP
MVP

Re: Sum of sales of previous year MTD

Let's take a step back... today is 01/17/2018... what would you want to see when nothing is selected? Now if you select 05/20/2017, what would you like to see

satish25
New Contributor III

Re: Sum of sales of previous year MTD

When nothing is selected, It should show me Sum(Budget_Sales) from 1/1/2017 to 12/31/2017

When I select 05/20/2017, it should show me Sum(Budget_Sales) from 1/1/2017 to 05/20/2017,

and when i select Month as May (No other selections), it should show me Sum(Budget_Sales) from 1/1/2017 to 05/31/2017