Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

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.

10 Replies
deepakk
Valued Contributor III

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

Not applicable

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.

dennisnet
Valued Contributor III

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.

MVP
MVP

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.

deepakk
Valued Contributor III

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.

Not applicable

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

MVP
MVP

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.

Not applicable

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.

MVP
MVP

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.

Community Browser