topic Re: looking to compute difference between columnvalue in pivot view in QlikView

looking to compute difference between columnvalue in pivot view

Rkota — Tue, 21 Nov 2017 19:10:12 GMT

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

Re: looking to compute difference between columnvalue in pivot view

sunny_talwar — Tue, 21 Nov 2017 19:16:59 GMT

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

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

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

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

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

Re: looking to compute difference between columnvalue in pivot view

Rkota — Tue, 21 Nov 2017 19:30:11 GMT

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.

Re: looking to compute difference between columnvalue in pivot view

sunny_talwar — Tue, 21 Nov 2017 19:45:17 GMT

Previous month against current month?

Re: looking to compute difference between columnvalue in pivot view

Rkota — Tue, 21 Nov 2017 19:52:07 GMT

yes please...and make that dynamic - thanks

here's the expression I was using before

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

Re: looking to compute difference between columnvalue in pivot view

sunny_talwar — Tue, 21 Nov 2017 19:57:30 GMT

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...

Re: looking to compute difference between columnvalue in pivot view

sunny_talwar — Tue, 21 Nov 2017 19:58:15 GMT

Also, how do you created Monthmoevment in the script?

Re: looking to compute difference between columnvalue in pivot view

Rkota — Tue, 21 Nov 2017 20:05:57 GMT

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.

Re: looking to compute difference between columnvalue in pivot view

sunny_talwar — Wed, 22 Nov 2017 13:49:07 GMT

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....?

Re: looking to compute difference between columnvalue in pivot view

Rkota — Wed, 22 Nov 2017 15:46:25 GMT

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.

Re: looking to compute difference between columnvalue in pivot view

sunny_talwar — Wed, 22 Nov 2017 16:05:35 GMT

If you are looking to find the difference in Period & Period - 1 and Period - 1 & Period - 2 and Period - 2 & Period - 3 and so on... why do you want to use set analysis? What issue are you seeing with using Above()/Below()/After()/Before() functions? A more optimized way is to use The As-Of Table if you are seeing performance issue... but in this scenario, set analysis is not going to work.