topic Excluding a dimension value in a pivot table? in QlikView

Excluding a dimension value in a pivot table?

Wed, 19 Oct 2011 13:01:25 GMT

Hello,

I've a problem with a Set Analysis expression and no idea how to resolve it.

I created some basics data for testing as follow:

Test:

LOAD * INLINE [

Project, Cost

Saturne, 1

Jupiter, 1

Neptune, 1

Pluton, 1

];

Now, I'm trying to have a table as :

Column 1: Project

Column 2: Sum(Cost) (with this example, it's 1).

Column 3: Sum(Cost) of all other projects (or in other words, All projects except the current one) (it should be 3).

I can't find any solution with set analysis or other method to find on line 1 the sum for project Jupiter, Neptune and Pluton. I can't actually select {All Projects minus/except Saturne}.

I hope you find this clear. Anyone have an idea for helping?

Thank you!

Excluding a dimension value in a pivot table?

sbaldwin — Wed, 19 Oct 2011 13:04:44 GMT

Hi, have you tried the formula:

=sum(TOTAL Cost) - sum(Cost)

Thanks

Steve

Excluding a dimension value in a pivot table?

Wed, 19 Oct 2011 14:48:39 GMT

Thanks for your reply.

My bad! My example is way too simple for modelling my problem. In my real model, I've others dimensions that must not be ignored for the cost aggregation.

But your answer, in its simplicity, might help me if I can have this TOTAL depending on my others dimensions but not the dimension "Project".

I'll post my solution if I find one. Until then, I'm happy to have new ideas.

Excluding a dimension value in a pivot table?

Thu, 08 Mar 2012 16:27:27 GMT

Just a follow-up I've never took take to post.

My understanding is that in a table, you can't actually select other values of a field specified in a rox of the table, which implied my problem.

My solution consisted in modifying my script so I can have this value directly.

I don't know if it's the best one, but it worked in my case.

Thanks for your help.