topic Re: Re: getting previous month values from yearmonth column in QlikView

getting previous month values from yearmonth column

rsdhavle — Thu, 20 Mar 2014 13:52:13 GMT

We have one yearmonth field in a format like 201403, 201402. On the basis of these values we are trying to get values as Mar14,Feb14 etc. When we use function month(now(yearmonth))&year(now(yearmonth)) it gives output as Mar2014 but when we try to calculate previous month by using function month((now(yearmonth-1)))&year(now(yearmonth)) to get output as Feb2014 it gives value as Mar2014 again.

Kindly guide what value should we use so that we get values of previous months like Feb14, Jan14, Dec13 etc..

Re: getting previous month values from yearmonth column

Nicole-Smith — Thu, 20 Mar 2014 13:56:32 GMT

I would suggest using date functions so it will treat your values as dates instead of text.

Current:

date(date#(yearmonth&'01', 'YYYYMMDD'), 'MMMYY')

date(addmonths(date#(yearmonth&'01', 'YYYYMMDD'),-1), 'MMMYY')

Re: getting previous month values from yearmonth column

rsdhavle — Thu, 20 Mar 2014 13:59:15 GMT

Perfect..Thanks Nicole!!

Re: getting previous month values from yearmonth column

rsdhavle — Thu, 20 Mar 2014 14:01:53 GMT

in addition to that lets say we have one revenue field and on the basis of above values we need to calculate Sum(Rev) for particular values like Mar14, Feb14 etc. How can we do that? What would be the correct expression

Re: getting previous month values from yearmonth column

jolivares — Thu, 20 Mar 2014 14:03:37 GMT

There are many ways to do that, but try to a different approach.

Assume that yourDate = 201403

First 201403, this is not a date, change to Date#(yourDate&'01','YYYYMMDD'), now you have a date 2014/03/01

Let's apply the function MonthName(Date#(yourDate&'01','YYYYMMDD')) = Mar 2014 (this is a date),

Now if you want last month you can do this

MonthName(AddMonths(Date#(yourDate&'01','YYYYMMDD'),-1)) = Feb 2014

Re: getting previous month values from yearmonth column

Nicole-Smith — Thu, 20 Mar 2014 14:04:18 GMT

What would the dimension be? (It can't be the date field if you want to use a separate expression for current and previous.)

Re: getting previous month values from yearmonth column

rsdhavle — Thu, 20 Mar 2014 14:06:41 GMT

I have different dimesnsions lets say client. And for client C1 i need to calculate revenue for currecnt month mar14 and in next column for feb14 and Jan14 etc..just the rquirement is it should start from the latest month in the data and based on that the previous values

Re: Re: getting previous month values from yearmonth column

Nicole-Smith — Thu, 20 Mar 2014 14:18:17 GMT

Create a Pivot Table.

First Dimension: Client

Second Dimension: date(date#(yearmonth&'01', 'YYYYMMDD'), 'MMMYY')

Expression: sum(Revenue)

Then pivot (drag) the second dimension (date column) so that the dates go across the top and create columns.

Example file is attached.