Dynamic Date

[#1]

Hi I am trying to get a dynamic date for an expression I am building.

We use Set Analysis so my code is

sum({$<
[Bill Date]= {"<=$(=date(max(Date)))>=$(=date(min(Date)))"},
Year = {'2016'},
Period = {*},
Date = {*},
Month = {'Aug'},
[Calendar Year] = {*},
QuarterName = {*},
[LinkId] = {"FIN"}
>}
[Bill Value] * $(RevenueRate))

which is perfect for August 2016 but no good for any other dates. In September I would like to report for all of August and in October I would to report for September etc.

I can get the current month using ={month(Max([Bill Date]))},

But I can't put this in the expression.

Any help would be gratefully appreciated

[shun_wong]

Hi Dave,

Can you try enclosing your current month expression in quotes?

={'month(Max([Bill Date]))'},

If this doesnt work, would you be able to provide a sample app?

Thanks,

Shun

[sunny_talwar]

Can you elaborate on how this is good for August? I guess I don't really understand where you are using this?

[#4]

the dates for August 2016 are hard coded into the expression

[Anonymous]

sum({$<
[Bill Date]= {"<=$(=date(max(Date)))>=$(=date(min(Date)))"},
Year = {'2016'},
Period = {*},
Date = {*},
Month = {'Aug'},                        // This will only ALLOW August month's (dates) 
[Calendar Year] = {*},
QuarterName = {*},
[LinkId] = {"FIN"}
>}
[Bill Value] * $(RevenueRate))

so if you  would like to have LAST months  data you can try like this !!!

Date(MonthStart(Max(Date), -1)  ........................will give previous month start

Date(MonthEnd(Max(Date), -1), ......................................will give previous month end dates

sum({$<

[Bill Date]= {"$(='>=' & Date(MonthStart(Max([Bill Date]), -1), 'MM/DD/YYYY') & '<=' & Date(MonthEnd(Max([Bill Date]), -1), 'MM/DD/YYYY'))"}>},

Year = {'2016'},

Period = {*},

Date = {*},

//Month = {'Aug'},

[Calendar Year] = {*},

QuarterName = {*},

[LinkId] = {"FIN"}

>}

[Bill Value] * $(RevenueRate))

[sunny_talwar]

Right, I see that, but what I don't understand is the fact that do you want to see most recent month's data based on today's month? Is that the goal here?

[#7]

That doesn’t seem to work.

How do  I provide a sample app, please excuse my ignorance!!

[#8]

yeah current months data

[shun_wong]

If you click on reply...

Use Advanced Editor:

then bottom right theres an attach button...

Attach your QV app.

[sunny_talwar]

How about this?

Sum({$<[Bill Date]= {"<=$(=Date(MonthStart(Today()), 'DateFieldFormatHere'))>=$(=Date(MonthEnd(Today()), 'DateFieldFormatHere'))"}, Year, Period, Date, Month, [Calendar Year], QuarterName, [LinkId] = {"FIN"}>} [Bill Value] * $(RevenueRate))