topic sum for previous month/(previous YearMonth) in QlikView

sum for previous month/(previous YearMonth)

Sun, 27 Sep 2015 06:23:24 GMT

Hello All

I am facing issue with below requirement.

I have a expression as 1) sum( {<Type={'123'}>} sales).

This give me the sum for YearMonth selected.

I need to create one more field which will provide the sum for previous month/(previous YearMonth).

I am getting that using addmonths(YearMonth, -1). However I am getting error while I integrate this in above expression (1).

I am trying something like -- > sum( {<Type={'123'}, YearMonth= addmonths(YearMonth, -1) >} sales).

But it is not working .

Please help me correct above expression. In Expression 1) I need to add logic for previous YearMonth ...

Thanks in advance....

Re: sum for previous month/(previous YearMonth)

sunny_talwar — Sun, 27 Sep 2015 16:04:37 GMT

Are you using this in a chart where YearMonth is a dimension?

Re: sum for previous month/(previous YearMonth)

maxgro — Sun, 27 Sep 2015 16:13:07 GMT

maybe you need to format the result of addmonths as your YearMonth field, example:

sum({$ <Type={'123'}, YearMonth={'$(=date(addmonths(max(YearMonth),-1),'YYYY MM'))'}>} sales)

Re: sum for previous month/(previous YearMonth)

pamaxeed — Sun, 27 Sep 2015 18:17:29 GMT

Hi,

i think it is a formatting problem like maxgro stated.

Try to give a look to the sample attached, I am using a variable to hold the previous month in the following format:

YYYY-MM.

Cheers,

Patric

Re: sum for previous month/(previous YearMonth)

simenkg — Sun, 27 Sep 2015 22:08:50 GMT

I usually do this by creating a MonthCounter-field in my master calendar.

Calendar:

load %Datekey,

%Datekey as Date,

Year(%DateKey)&'-'&Month(%Datekey) as YearMonth,

Autonumber(Year(%DateKey)&'-'&Month(%Datekey),'MonthCounter') as MonthCounter

resident tempDates order by %DateKey asc;

Then you can use the expression

sum({$ <Type={'123'}, YearMonth=, MonthCounter={$(=Max(MonthCounter)-1)}>} sales)

Regards

SKG

Re: sum for previous month/(previous YearMonth)

Anonymous — Mon, 28 Sep 2015 07:06:04 GMT

Yes. I am using in chart as a Set analysis expression.

Re: sum for previous month/(previous YearMonth)

Anonymous — Wed, 30 Sep 2015 18:22:58 GMT

Thanks a lot Maxgro.. It worked for me with slight change.

Can you please explain me how/where to use the $ and '=' which u used before date. I do not understand how to write these set analysis explression.

Re: sum for previous month/(previous YearMonth)

maxgro — Thu, 01 Oct 2015 07:29:43 GMT

dollar sign expansion

I can't explain better than Henric

The Magic of Dollar Expansions