Formula help - MonthsStart?

datanibbler — Fri, 12 Jul 2013 07:44:30 GMT

Hi,

a little background on what I want to do with this expression:

I have on one worksheet two pivot_tables (looking like two calendar_sheets). Currently the logic is this:

- Upon opening the sheet, the current month is automatically selected.

- The upper calendar_sheet then displays the days (Mo-Sat) of the current month, with data from an underlying list in the corresp. field.

- The lower one displays the same for the past month.

- The problem that I'm just trying to avoid is that all this currently works only when a selection has been made - it is a condition for recalculation that exactly one
month be selected. The formula in the lower diagram thus looks very different from the one in the upper diagram, making it very complicated to implement new
features.

After realizing that I cannot combine the MONTHSTART and MONTH() functions, I have tried MONTHsSTART. That seems to work, by trial-and-error I have found out how I can display the first and last week of this or the past month. I hope to implement this as a condition in the "week" dimension of both diagrams and thus get the same result without any selection.

I would, however, like to understand what the function does - I have played around with the parameters and I can write either >MonthsStart(2, TODAY()), -1)< OR >MonthsStart(1, TODAY(), -2)<, both will give me the 1st of May (two months back).

Can someone clarify this function for me, please?

Thanks a lot!

Best regards,

DataNibbler

Re: Formula help - MonthsStart?

swuehl — Fri, 12 Jul 2013 08:06:35 GMT

After realizing that I cannot combine the MONTHSTART and MONTH() functions, I have tried MONTHsSTART.

Sorry, I don't get why you can't combine these functions. Monthsstart() is not what you need here, Monthstart() is much more simple.

=monthstart(today(),-2)

Re: Formula help - MonthsStart?

datanibbler — Fri, 12 Jul 2013 10:21:46 GMT

Hi swuehl,

you're right. I was not aware that I could specify a second parameter for the Monthstart() function, but that works fine. I guess it is also better on the performance side, MonthsStart calculates with a millisecond´, that sounds kind of resource-heavy to me...

Thanks a lot!

Best regards,

DataNibbler

topic Re: Formula help - MonthsStart? in QlikView

Formula help - MonthsStart?

Re: Formula help - MonthsStart?

Re: Formula help - MonthsStart?