topic Re: Calculation Of current and Previous Month in QlikView

Calculation Of current and Previous Month

mario-sarkis — Tue, 28 Jul 2015 14:12:38 GMT

Please find attachement my sample data :

i need to calculate the sum of amount of max periodDate (of current Date)

and compare it with the Previous month

ID SumAmountMaxPeriodDate SumAmountPerviousMonth

this will b clear when the sample data that i have

sum({<[PeriodDate]={$(=max([PeriodDate]))}>}[Amount] it give me a Sum of CurrentMonth (as max periodDate) and

for the Previous Month i tried this expression

sum({<[PeriodDate]={$(=num(monthend(AddMonths(max([PeriodDate]),-1))))}>}[Amount]) it give me zero

Re: Calculation Of current and Previous Month

Gysbert_Wassenaar — Wed, 29 Jul 2015 13:38:11 GMT

MonthEnd will return the last second of a month: i.e. something like 01-01-2015 23:59:59. Your PeriodDate values won't have that time fraction. If you want the last day of the previous month try $(=num(MonthStart(max([PeriodDate]))-1) )

Re: Calculation Of current and Previous Month

sunilkumarqv — Wed, 29 Jul 2015 13:42:49 GMT

Try like this

sum({<[PeriodDate]={">=$(=max([PeriodDate])-1)"}>}[Amount]

Re: Calculation Of current and Previous Month

mario-sarkis — Wed, 29 Jul 2015 14:37:39 GMT

hi Gysbert should i add AdMonths() before the periodeDate?

Re: Calculation Of current and Previous Month

ramoncova06 — Wed, 29 Jul 2015 14:43:16 GMT

you can use floor to get rid of the fractions from the month end function, and when using monthend you can define the offset

sum({<[PeriodDate]={$(=num(floor(monthend((max([PeriodDate]),-1))))}>}[Amount])

Re: Calculation Of current and Previous Month

mario-sarkis — Wed, 29 Jul 2015 14:47:48 GMT

Hi Ramon i tried this Expression and it give me Zero anything wrong in the syntax ?

sum({<[PeriodDate]={$(=num(floor(monthend(max([PeriodDate]))-1)))}>}[Amount])

Re: Calculation Of current and Previous Month

mario-sarkis — Wed, 29 Jul 2015 15:06:36 GMT

Hi Sunil Kumar this is giving me the Sum of Amount of the Current month not the Previous one

Can you help to find the sum of Amount of the Previous Month ?

Re: Calculation Of current and Previous Month

mario-sarkis — Wed, 29 Jul 2015 15:07:18 GMT

this expression is givin me Zero

Re: Calculation Of current and Previous Month

ramoncova06 — Wed, 29 Jul 2015 15:25:48 GMT

you are missing the coma for the monthend function

sum({<[PeriodDate]={$(=num(floor(monthend(max([PeriodDate]),-1))))}>}[Amount])