Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

thanks in advance

Thomas

1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

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.

View solution in original post

10 Replies
deepakk
Partner - Specialist III
Partner - Specialist III

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

Not applicable
Author

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
Not applicable
Author

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
Partner - Specialist III
Partner - Specialist III

hi Thomas,

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

Not applicable
Author

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.

Not applicable
Author

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

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.