topic Re: addmonths query in QlikView

addmonths query

hopkinsc — Wed, 25 Nov 2020 16:16:04 GMT

Hi All

i cant get something working.

If a user was to select a fiscal year of 2016, then i want the text box to show the sum(sales) but for the previous year but the following month based on todays date.

=Sum({<[Fiscal Year]= {$(=max([Fiscal Year])-1)}, Month={$(=Date(Date#(Month(today())+1, 'MM'),'MMM'))}>}[Sales])

Can anyone help?

Re: addmonths query

sunny_talwar — Wed, 16 Mar 2016 11:06:59 GMT

May be this:

=Sum({<[Fiscal Year] = {$(=Max([Fiscal Year])-1)}, Month={'$(=Month(AddMonths(Today(), 1)))'}>} Sales)

Re: addmonths query

Anonymous — Wed, 16 Mar 2016 11:08:12 GMT

did you check this?

Current Month v. Same Month Last Year

Re: addmonths query

MayilVahanan — Wed, 16 Mar 2016 11:13:22 GMT

Try like this

=Sum({<[Fiscal Year] = {$(=Max([Fiscal Year])-1)}, Month={'$(=Month(AddMonths(Today(), 1)))'}>} Sales)

Re: addmonths query

hopkinsc — Wed, 16 Mar 2016 11:28:06 GMT

that doesnt work but i think its because my month field is in the number format. i.e.

etc

Re: addmonths query

sunny_talwar — Wed, 16 Mar 2016 11:29:36 GMT

Try this:

=Sum({<[Fiscal Year] = {$(=Max([Fiscal Year])-1)}, Month={'$(=Num(Month(AddMonths(Today(), 1))))'}>} Sales)

Re: addmonths query

MayilVahanan — Wed, 16 Mar 2016 11:33:54 GMT

Then Change Month to Number by using Num() function

Re: addmonths query

MayilVahanan — Wed, 16 Mar 2016 11:34:43 GMT

Sunny is too fast to reply

Please follow sunny suggestion.