Set expressions for current Quarter and Month sales.

[maahivee]

Hello friends,

I have a requirement in my project, Where I have to write a set expression to determine current month and current quarter sales

But the problem is that i have tried so many different expressions and is not able to achieve it.

i have tried it in these ways

=num(Sum({$<Month = {'$(=Num(month(today()-1)))'}>} [Extended Price] ) ,'$#,###.') keeping in mind that the date in my DB is in Number format. I don't know where and what am i doing wrong. But this is kind of an urgent requirement for me.. Please help.

[maahivee]

Hello Sunny- The problem is that for those expressions they are still showing last month and last quarter sales. No matter what. I dont know where it went wrong suddenly. Can you please help me one again.

[sunny_talwar]

Sure, but you will need to elaborate on what is happening.

[maahivee]

Sunny- The problem is that,

=Num(Sum({$<MonthYear = {"$(='>=' & Date(QuarterStart(AddMonths(Today(), -1)), 'MMM_YYYY') & '<=' & Date(QuarterEnd(AddMonths(Today(), -1)), 'MMM_YYYY'))"}>} [Extended Price] ) ,'$#,##0')

In this expression, we are using addmonths  function and since we have to give a parameter for addmonths function and that parameter here is -1, so it is adding up last month and last quater sales for both MTD and QTD. So i changed the parameter to be 0 now and it is giving the correct numbers, But the problem is that now i am not able to define the MTD sales for last year Say like sales from july 1st to july 7th of 2014.

Usually i do like this to determine last year sales

Sum({$<MonthYear = {"$(=Date(AddMonths(Today(), -1), 'MMM_YYYY'))"}>} [Extended Price] )

But in this case it is only giving last month sales. I know i am a bit sloppy here but if you can understand what i am trying to say, it is really nice.

[maahivee]

In short i am trying to say that i am not able to select max(year)-1 kind of filter in this expression.

[sunny_talwar]

‌if you give me the date ranges you want. I will make it to work.

lets assume the max date in your data is 06/30/2015. what ranges should your expression cover? give me different date ranges for different expressions.

[maahivee]

i am just trying to do like simple MTD expressions which is like starting from the 1st of the current month which is July now to the current date which is 8th but since we dont have the data for 8th, i want to show 1st jul to 7th jul of 2015 which i did using the expression

Sum({$<MonthYear = {"$(=Date(AddMonths(Today(), 0), 'MMM_YYYY'))"}>} [Extended Price] )

But now i want to do the same thing for the years 2014 and 2013.

[sunny_talwar]

Do you want these ranges if Max is 06/30/2015

1) 06/01/2015 - 06/30/2015

2) 06/01/2014 - 06/30/2014

3) 04/01/2015 - 06/30/2015

4) 04/01/2014 - 06/30/2014

All of the above?

[sunny_talwar]

Got it. Will send you what you need in a bit.

Best,

Sunny

[maahivee]

yes but just 1 and 2, not 3 and 4.

[sunny_talwar]

For 2015: 07/01/2015 - 07/08/2015

=Sum({$<MonthYear = {"$(='>=' & Date(MonthStart(Today()), 'MMM_YYYY') & '<=' & Date(Today(), 'MMM_YYYY'))"}>} [Extended Price])

For 2014:07/01/2014 - 07/08/2014

=Sum({$<MonthYear = {"$(='>=' & Date(MonthStart(AddYears(Today(), -1)), 'MMM_YYYY') & '<=' & Date(AddYears(Today(), -1), 'MMM_YYYY'))"}>} [Extended Price])

For 2013:07/01/2013 - 07/08/2013

=Sum({$<MonthYear = {"$(='>=' & Date(MonthStart(AddYears(Today(), -2)), 'MMM_YYYY') & '<=' & Date(AddYears(Today(), -2), 'MMM_YYYY'))"}>} [Extended Price])

I hope this is what you want?

Best,

Sunny