Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Can you elaborate on how this is good for August? I guess I don't really understand where you are using this?
the dates for August 2016 are hard coded into the expression
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))
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?
That doesn’t seem to work.
How do I provide a sample app, please excuse my ignorance!!
yeah current months data
If you click on reply...
Use Advanced Editor:
then bottom right theres an attach button...
Attach your QV app.
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))