topic Dynamic Date in QlikView

Dynamic Date

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Dynamic Date

-SW- — Mon, 15 Aug 2016 11:58:26 GMT

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

Re: Dynamic Date

sunny_talwar — Mon, 15 Aug 2016 12:00:59 GMT

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

Re: Dynamic Date

Mon, 15 Aug 2016 12:27:24 GMT

the dates for August 2016 are hard coded into the expression

Re: Dynamic Date

Anonymous — Mon, 15 Aug 2016 13:07:47 GMT

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))

Re: Dynamic Date

sunny_talwar — Mon, 15 Aug 2016 13:27:03 GMT

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?

Re: Dynamic Date

Mon, 15 Aug 2016 13:27:59 GMT

That doesn’t seem to work.

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

Re: Dynamic Date

Mon, 15 Aug 2016 13:28:28 GMT

yeah current months data

Re: Dynamic Date

-SW- — Mon, 15 Aug 2016 15:17:39 GMT

If you click on reply...

Use Advanced Editor:

then bottom right theres an attach button...

Attach your QV app.

Re: Dynamic Date

sunny_talwar — Tue, 16 Aug 2016 01:33:25 GMT

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))

Re: Dynamic Date

Tue, 16 Aug 2016 10:03:42 GMT

I used

Sum({<[Bill Date]={">=$(=MonthStart(Today(),Day(Today())=1))"} ,
thanks for your help