10 Replies Latest reply: Feb 14, 2011 12:44 PM by Thomas Eyck

# Listbox with Date - how to calculate month and month-1

I have a pivot-table where I calulcate =sum(Value) to see the sum of different dates from a listbox. Now I want to calculate the value of the selected month and the month - 1 or from my listbox the value from selected Item - 1

I tried everything, analysis sets, monthadd and other functions, but I never get a useful result. How can I calculate the sum from the month-1 or even month-2 and so on...

Thomas

• ###### Listbox with Date - how to calculate month and month-1

hi,

Create a MonthNum field as Num(Month(Date)) as MonthNum and then use this expression

(Sum({<MontNum={\$(=Max(MonthNum)-1)}>} Amount)

I hope this helps

• ###### AW:Re: Listbox with Date - how to calculate month and month-1

Thanks, I tried that but the result is always '0'.

The same with my analysis set:

=sum({\$ < DATE = {\$(#=addmonths(getfieldselections(DATE),-1))}>} VALUE)

The function for the month-1 is working (checked it with a textbox) but my analysis set is not working, giving me the result = 0. Maybe a wrong syntax? The simple function =sum(Value) is fine.

Thanks.

• ###### AW:Re: Listbox with Date - how to calculate month and month-1

Hi Thomas,

Can you upload the file, so we can have a look?

Dennis.

• ###### AW:Re: Listbox with Date - how to calculate month and month-1

Hello Thomas,

I'd suggest to select the month from a variable instead of the field itself. Anyway, Is the AddMonths function returning the same format that the field "DATE" is? Check that in a separate text object, and do any formatting required in the script, if possible.

Hope this helps.

• ###### AW:Re: AW:Re: Listbox with Date - how to calculate month and month-1

Thanks a lot.

The formats looks the same, both textboxes are showing a date like dd.mm.yyyy - e.g. 01.05.2009 and the second textbox (Variable vDate) is showing perfectly 01.04.2009.

I am using a variable now (vDate) witch contains the function =(addmonths(getfieldselections(DATE),-1,0)).

and my analysis set is:

=sum({\$ < DATE = {\$(#vDate)}>} Value)

The result in the pivot table is '-'.

• ###### AW:Re: AW:Re: Listbox with Date - how to calculate month and month-1

Hello Thomas,

It seems that AddMonths function requires a date formatting:

`=AddMonths(Date#('01.04.2009', 'DD.MM.YYYY'), -1, 0)`

While

`=AddMonths('01.04.2009', -1, 0)`

doesn't work.

Hope this helps.

• ###### AW:Re: AW:Re: AW:Re: Listbox with Date - how to calculate month and month-1

I tried the Date# but this also doesn't work. I uploaded the appication so you can take a look. Could it be my Version of QV? I am working with Version 9 Release 5.

• ###### AW:Re: AW:Re: AW:Re: Listbox with Date - how to calculate month and month-1

Hello Thomas,

I think it's just a question of formatting and quoting:

`sum({< DATE = {'\$(vDate)'}>} AZ_WERT)`

Prefixing with "#" the variable name means the result is numeric, and in this case, it's not. Hence single quoting is needed.

Hope that helps.

• ###### AW:Re: AW:Re: AW:Re: AW:Re: Listbox with Date - how to calculate month and month-1

PERFECT, this is working great

sum({< DATE = {'\$(vDate)'}>} AZ_WERT)

Thank you very much.

• ###### Listbox with Date - how to calculate month and month-1

hi Thomas,

I missed to suppres the Month field in the above expression. I have attach the application. I hope this helps.