Logic Help

[abhaysingh]

Hey Guys,

Need Some simple logic syntax help

I have to show MTD so i did this like below

>=monthstart(max(date)) <= Monthend(max(date))

which is working fine..

but in my data the max Date is future date like after 2 month in my case.

so what i want is by default dashboard shud work on today() condition and if user select anything than it shud work on my normal condition described above.

Today condition i hope would be like this.

>=monthstart(today()) <= Monthend(today()).

i think i can write the condition using Getfieldselection, but i need syntax help, or any other input appreaciated

[MK_QSL]

Sum({<Date = {">=$(=Date(MonthStart(Today())))<=$(=Date(Today()))"}>}Value)

This will give you Month To Date

If you want MonthStart to MonthEnd, use below

Sum({<Date = {">=$(=Date(MonthStart(Today())))<=$(=Date(MonthEnd(Today())))"}>}Value)

[sunny_talwar]

May be this:

{<date = {"$(='>=' & MonthStart(If(GetSelectedCount(Date) = 0, Today(), Max(date))) & '<=' & MonthEnd(If(GetSelectedCount(Date) = 0, Today(), Max(date))))"}>}

[abhaysingh]

Hi Manish,

What i want is by default it shud give me current month to Date and if user select any backdate month or year , user shud able to see the data for selected month as MTD and Respected back month as LMTD

[MK_QSL]

I assumed that you are giving Month and Year fields to user for selection.

=If(GetSelectedCount(InvoiceYear) = 0 and GetSelectedCount(InvoiceMonth) = 0, SUM({<InvoiceDate = {">=$(=Date(MonthStart(Today())))<=$(=Date(Today()))"}>}Sales),

SUM({<InvoiceYear, InvoiceMonth, InvoiceDate = {">=$(=Date(MonthStart(MakeDate(Max(InvoiceYear),Max({<InvoiceYear = {'$(=Max(InvoiceYear))'}>}InvoiceMonth)))))<=$(=Date(MonthEnd(MakeDate(Max(InvoiceYear),Max({<InvoiceYear = {'$(=Max(InvoiceYear))'}>}InvoiceMonth)))))"}>}Sales))

Change...

1) InvoiceYear... InvoiceMonth.... and Sales Field according to your application fieldnames.

2) If you want to ignore some fields like Week, Quarter, add them in Set Analysis field as Week=, Quarter=

For Previous Month ... use

=If(GetSelectedCount(InvoiceYear) = 0 and GetSelectedCount(InvoiceMonth) = 0, SUM({<InvoiceDate = {">=$(=Date(MonthStart(Today(),-1)))<=$(=Date(AddMonths(Today(),-1)))"}>}Sales),

SUM({<InvoiceMonth = , InvoiceYear = , InvoiceDate = {">=$(=Date(MonthStart(MakeDate(Max(InvoiceYear),Max({<InvoiceYear = {'$(=Max(InvoiceYear))'}>}InvoiceMonth)),-1)))<=$(=Date(MonthEnd(MakeDate(Max(InvoiceYear),Max({<InvoiceYear = {'$(=Max(InvoiceYear))'}>}InvoiceMonth)),-1)))"}>}Sales))

[abhaysingh]

Hi Manish,

these condition perfectly working on default Current and Last month, but when i m selecting any backdate its not working..

Any suggestion?

[MK_QSL]

What is your selection fields? What format they are? Are they derived from date fields?

[sunny_talwar]

Have you tried what I proposed may be?