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

Comparison Month <-> previous month

Hi all,

I tried to find a solution within the community group here but didn't find any solution which works for me.

I have a table like following:

MyDate           Value     Type

01.10.2012     10.000     Car

01.10.2012         500     Bike

01.11.2012     12.000     Car

01.11.2012       1.200     Mini

01.11.2012          600     Bike

I want to have a straight table with date in the Dimension MyDate and the expressions value, prev.month and Difference in the lines.

                         Oct 2012          Nov 2012      ....

Value                   10.500               13.800     .....

prev.Month               0                   10.500     ....

Difference            10.500                 3.800     .....

I created a new Variable vMonthMinus1 like this: =date(addmonths(MyDate,-1),'YYYY-MM')

In my expression I tried to have the following:

=sum({<MyDate=vMonthMinus1>}[Value])

Unfortunetly I got only 0 as result. Can someone help me ?

Many thanks in advance from germany

Markus

1 Solution

Accepted Solutions
Gysbert_Wassenaar

You're using Month as a dimension and then set analysis expressions won't help you. The set is calculated once for the entire chart, not per dimension value. You could try using the before() function.

Value: sum(Value)

prev.Month: before(sum(Value)

Difference: Value - [prev.Month]

The disadvantage is that if you select only Nov 2012 you won't get a result for prev.Month since Oct 2012 isn't in your chart anymore.


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
Gysbert_Wassenaar

You're using Month as a dimension and then set analysis expressions won't help you. The set is calculated once for the entire chart, not per dimension value. You could try using the before() function.

Value: sum(Value)

prev.Month: before(sum(Value)

Difference: Value - [prev.Month]

The disadvantage is that if you select only Nov 2012 you won't get a result for prev.Month since Oct 2012 isn't in your chart anymore.


talk is cheap, supply exceeds demand
alexandros17
Partner - Champion III
Partner - Champion III

add a new column in the table where you compute previous month then left join this table with the same table on previous month of the first table = month of the second one.

this adds value columns for previous month ...

Hope it helps

alexandros17
Partner - Champion III
Partner - Champion III

Sorry Gysbert, I did not see your answer (exact as usual)

Alexandros17

Not applicable
Author

Hi Gysbert,

great - it works. Just so simple and I didn't "saw" it.

Best regards

Markus