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

[#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...

thanks in advance

Thomas

[deepakk]

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

[#3]

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.

[Anonymous]

Hi Thomas,

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

Dennis.

[Miguel_Angel_Baeyens]

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.

[deepakk]

hi Thomas,

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

[#7]

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 '-'.

[Miguel_Angel_Baeyens]

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.

[#9]

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.

[Miguel_Angel_Baeyens · Accepted solution]

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.