looking to compute difference between columnvalue in pivot view

[Rkota]

In this pivot view, I'm trying to compute the difference between the months for each line of the update range.  I'm looking to get the absolute value.  Here is the dimension and expression (just a simple count on the dimension shown in parenthesis.

 

the expression I'm using to compute totals by moth is not working - it includes all months.  Maybe my approach is wrong.

Sample for on month

Count({<Movemonth = {"$(='>=' & Date((AddMonths(Today(),-3)),'MMMMM'))"}>} unittranid)

Any help/tips to fix this issue would be greatly appreciated.

Thx

[sunny_talwar · Accepted solution]

Not entirely sure, but are you looking to get this?

Count(unittranid) - After(Count(unittranid))

or

Count(unittranid) - Before(Count(unittranid))

or

After(Count(unittranid)) - Count(unittranid)

or

Before(Count(unittranid)) - Count(unittranid)

[Rkota]

Worked!!!  Had no idea that such a function called after, before exists.

Thanks a ton for the super timely help.

on another note, how do I count for a the previous month - with/without putting in the literal.  I.E as of today the three prior months are Aug, Sep and Oct.   What would be the expression to get the count for each of these months using an expression

Again, many thanks for your timely help Sunny.

[sunny_talwar]

Previous month against current month?

[Rkota]

yes please...and make that dynamic - thanks

here's the expression I was using before

Count({<Movemonth = {"$(='>=' & Date((AddMonths(Today(),-3)),'MMMMM'))"}>} unittranid)

[sunny_talwar]

Anytime you want to bring Previous Month to Today's month, you need to use Above, Below, After, Before function, set analysis is not really a good idea to use here...

[sunny_talwar]

Also, how do you created Monthmoevment in the script?

[Rkota]

thanks again Sunny.  Am beginning to see the same that doing these in qlikview is more work than necessary.  Most of what I do is when I load data from SQL server...I try and use qlik only for presentation.

the Move month based on datename(MONTH, ...) in sql.

should I assume this is better off done when extracting data from the backend?  Defer to your opinion since you are a Qlik expert.

[sunny_talwar]

Well, here are few things I want to know

1) Are you looking to only look at Period and Period -1 in a chart?

2) Period-1 is shown against Period -1 or Period? (For example, when we did Period - Period-1, we had to bring Period-1 in the same column as Period. Is that what you want?)

3) Movemonth is only month or does it have the date information in it? To check this, create a list box for Movemonth and give it a number representation from the number tab to see if it is a number like 41xxx or is it showing 1, 2, 3, 4 or does it not change at all....?

[Rkota]

thanks for the note Sunny.

If I understand you right, here is some feedback to your questions.

1. I'm assuming by Period and Period-1, you mean August, September etc., in my pivot view.  So, yes, I am trying to compute the difference (increase/decrease) between period 1 and period.  Similarly period - 2 and period - 1

2. Movemonth is a string as, I calculate that as part of my SQL output.

Again, thanks for your help.