topic Re: Dynamic Dashboard Dates in QlikView

Dynamic Dashboard Dates

Thu, 28 Jan 2016 06:26:49 GMT

Hi Community

I have a field "Budget Month", which takes the year, then the month from the 26th to the 25th of the following month e.g my current budget month is "2016 Jan - Feb" and before the 26th it was "2016 Dec - Jan" for dates that fall in Jan and "2015 Dec - Jan" for dates that fall in Dec. Now I would like to get the previous budget month. This must change dynamically as I make date selections and as time goes

Currently it only works correctly when I have a date selected and I would like it to work exactly as my current budget month. Can anyone please assist?

Re: Dynamic Dashboard Dates

jonathandienst — Thu, 28 Jan 2016 06:51:42 GMT

I suggest that you add something like the following fields to your master calendar:

MasterCalendar:

LOAD

CalDate As Date,

...

If(Day(CalDate) <= 25, Date(CalDate, 'YYYY MMM') & ' - ' & Date(AddMonths(CalDate, 1), 'MMM'),

Date(AddMonths(CalDate, 1), 'YYYY MMM') & ' - ' & Date(AddMonths(CalDate, 2), 'MMM')) As Period,

AutoNumber(If(Day(CalDate) <= 25, Date(CalDate, 'YYYY MMM') & ' - ' & Date(AddMonths(CalDate, 1), 'MMM'),

Date(AddMonths(CalDate, 1), 'YYYY MMM') & ' - ' & Date(AddMonths(CalDate, 2), 'MMM'))) As PeriodSeq,

...

(Assumes you have a master calendar load. Adjust the script to change CalDate to the base date in your calendar)

Now you can find the previous period using =PeriodSeq - 1

If you don't have a master calendar, you can search this site for more information

Re: Dynamic Dashboard Dates

Thu, 28 Jan 2016 07:11:19 GMT

Thanks a lot Jonathan! This works perfectly