topic Adding Columns in the Pivot Table in QlikView

Adding Columns in the Pivot Table

Wed, 10 Feb 2010 15:15:05 GMT

Hi All,

Look the attached QVW file.

Is it possible to combine table 2, table 3 and table 4 with table1. In table1, I need to have three more columns to be added at the end like Sum(F3), Count(F3), Avg(F3). But if you see I have table2, table3 and table4 with only one dimension as F2. Is it logically possible to get the desired result. I use 8.2 version.

Adding Columns in the Pivot Table

Wed, 10 Feb 2010 15:46:39 GMT

I don't think there is a nice or easy way to do it. I'd really like to see a more flexible table builder in a future QlikView version (see MS SSRS).

With a combination of another Inline Load and some modifications to the chart (see the Expression and the Sort Expression on Name), you may be able to put together something that works.

I've attached my sample.

Adding Columns in the Pivot Table

Wed, 10 Feb 2010 17:05:39 GMT

Fantastic. I will try with my original document. Thanks.

Adding Columns in the Pivot Table

Sun, 14 Feb 2010 16:38:10 GMT

Hi NMiller,

I tried with my original data. To my surprise, I am not able to perform the same operation as you did. Can you please see the attached file.

Adding Columns in the Pivot Table

Mon, 15 Feb 2010 21:07:48 GMT

I'm not sure why it doesn't work, but I think it has to do with aggregation.

Instead of: If(week='Avg'...
You need: If(Concat(TOTAL <week> week)='Avg'

I tried a few other things, but that was the first thing that worked. It seems a little weird, like there should be a better way to do it, but it works.

Here's the entire Amount expression:

=If(Concat(TOTAL <week> week)='Avg', Avg(TOTAL <sv_user> q_id),
If(Concat(TOTAL <week> week)='Sum', Sum(TOTAL <sv_user> q_id),
If(Concat(TOTAL <week> week)='Count', Count(TOTAL <sv_user> q_id), count(q_id))))

EDIT: It seems the new design of the forum broke the file upload ability. Try using the expression, I'll try to post the file later if need be.

Adding Columns in the Pivot Table

Tue, 16 Feb 2010 15:53:04 GMT

Yes it works now. But, when I select any field (say through multibox) the sum, avg and count columns goes off. Is there any way of retaining it when selections are made.

Adding Columns in the Pivot Table

Tue, 16 Feb 2010 16:39:52 GMT

Yes, if you modify the Concat equation to add {1}. e.g.

=If(Concat({1}TOTAL <week> week)='Avg', Avg(TOTAL <sv_user> q_id),
If(Concat({1}TOTAL <week> week)='Sum', Sum(TOTAL <sv_user> q_id),
If(Concat({1}TOTAL <week> week)='Count', Count(TOTAL <sv_user> q_id),
count(q_id))))

That seems to work with selections on sv_user.

Adding Columns in the Pivot Table

Tue, 16 Feb 2010 17:14:09 GMT

I get an expression error when I used your expression. I use version 8.2. Is this creating problem?

Can i use for multiple selections as well with this formula.

Thanks.

Adding Columns in the Pivot Table

Tue, 16 Feb 2010 17:30:24 GMT

Yes, the last thing I added is Set Analysis, which is only available in version 8.5 or later. You need a way to ignore selections, because the dummy week fields are not associated with any sv_users. When you make a selection on sv_user that excludes those dummy fields. I don't know if that is possible without Set Analysis.

One thing you may try is instead of loading those three dummy fields as an inline load, do another load with each of your sv_users and each of the dummy fields.

Something like:

Dummy:
LOAD sv_user, 'Sum' As week
RESIDENT Test2;

And do that for each dummy field. Unfortunately, that doesn't load only unique instances of sv_user. It may be something to try though. Really, like everything else in QlikView, it's a lot easier with Set Analysis.

EDIT: If you are able to get records for each of your sv_users and each of the dummy fields as week, then it should work. If you do that, then you can go back to the original version of the expression I gave:

=If(week='Avg', Avg(TOTAL <sv_user> q_id),
If(week='Sum', Sum(TOTAL <sv_user> q_id),
If(week='Count', Count(TOTAL <sv_user> q_id), count(q_id))))

I'll see if I can upload a sample.