topic Re: Difference of two rows in QlikView

Difference of two rows

robert_mika — Fri, 26 Feb 2016 08:13:42 GMT

Is the a way to add an additional row to a table/pivot table?

My challenge is to take difference between two rows

Example below:

I need to know the difference between B and A for Mike and John.

Is it possible to do that in a table with expression or only script level?

Re: Difference of two rows

Anonymous — Fri, 26 Feb 2016 08:20:18 GMT

column(2)-column(1)

Re: Difference of two rows

sunny_talwar — Fri, 26 Feb 2016 08:22:47 GMT

You might be able to use ValueList here, have not tested but usually works with an Island table, but since you showed reluctance for script changes try this:

Pick(ValueList(1, 2), Opportunieties, 'Difference') -> as your dimension

Name

Expression should be something along these lines

Pick(ValueList(1, 2), 'However you would calculate the difference', YourCurrentExpression')

Re: Difference of two rows

petter — Fri, 26 Feb 2016 08:34:05 GMT

It could be done in this way:

Re: Difference of two rows

robert_mika — Fri, 26 Feb 2016 08:48:33 GMT

Thanks, Sunny.

Script changes can be an option

I added calcualted dimension but that does not bring any values

What I', missing here?

Do you have an example of this methode?

Re: Difference of two rows

petter — Fri, 26 Feb 2016 08:56:56 GMT

There is a typo in the expression for John... so the calculation is wrong. It should state John in both Sums of that expression - not mixing John and Mike...

Re: Difference of two rows

tresB — Fri, 26 Feb 2016 09:09:32 GMT

Is this your real case, i.e. - only two line items would be there in your table? I ask this because, I doubt the alternative approaches would be suggested be dynamic unless you go with script solution. And if you are okay with static solution, let know.

Re: Difference of two rows

tresB — Fri, 26 Feb 2016 09:20:48 GMT

May be like attached

Re: Difference of two rows

sunny_talwar — Fri, 26 Feb 2016 10:18:39 GMT

Another one by making changes to tresesco‌'s provided solution.

Re: Difference of two rows

sunny_talwar — Fri, 26 Feb 2016 10:20:16 GMT

Seems like ValueList doesn't work. I added a sample by modifying tresesco's qvw. See if that one seems like a viable solution.

Best,

Sunny

Re: Difference of two rows

robert_mika — Fri, 26 Feb 2016 10:25:23 GMT

Hi tresesco,

Your solution works.But as you mentioned there will be more item in the table.

Is there any other approach in case I will have up to 20 or more names?

Can the pick/match pull names from a list instead of static entries?

So for each name create the same expression but change that for the difference?

Many Thanks

Re: Difference of two rows

robert_mika — Fri, 26 Feb 2016 10:29:06 GMT

Just have seen that and testing...

Re: Difference of two rows

tresB — Fri, 26 Feb 2016 10:34:33 GMT

In my solution, pick(match.. is in fact picking the values from dimensions. But the challenge in this approach would come when the rows increase. That means, would you expect a difference row after every couple of rows? Or, how? A sample with at least two differences would help.

Re: Difference of two rows

robert_mika — Fri, 26 Feb 2016 12:40:09 GMT

Thank you all for the input.

Tresesco/Sunny your combined effort has brought the final solutions.

Thanks.