topic Re: Need help with pivot table in QlikView

Need help with pivot table

Fri, 19 Apr 2013 03:47:24 GMT

I need to show monthly product sales data by selected date range by the user.

It is quite easy to create a pivot table with my required 2 dimensions, 1 being the ProductName and the other being the Month.

So my pivot looks like

Product	Jan13	Feb13	Mar13	Apr13
PartA	13	9	20	16
PartB	43	21	28	40
PartC	19	6	17	18
PartD	27	51	32	29
PartE	10	8	6	11
PartF	17	22	23	37

So far no problems. The months shown above may vary depending on the range the user has selected, but it is basically the same.

But what I really need is to show extra columns which show current quantities for each item. These do not change depening on the month because they are static "as at now" or more correctly when the data was loaded. I cannot work out how to get these extra columns to display without Qlikview thinking I need to show them by period. I don't. They are just another display column associated with the product.

What I need is this:

product	Current Stock Qty	On backorder	On Purch Order	Jan 13	Feb 13	Mar 13	Apr 13
PartA	103	0	50	13	9	20	16
PartB	56	0	100	43	21	28	40
PartC	121	19	50	19	6	17	18
PartD	0	5	200	27	51	32	29
PartE	12	0	120	10	8	6	11

The months need to change if the user selects, say July 10 to March 11. But the 3 qantity columns do not change because they need to display up to date "now" data.

How would I do this?

Thanks in advance

Re: Need help with pivot table

Gysbert_Wassenaar — Fri, 19 Apr 2013 06:00:56 GMT

You can add them as calculated dimensions. For example =aggr(sum([Current Stock Qty]),product) for the current stock qty column.

Re: Need help with pivot table

Mon, 22 Apr 2013 00:42:28 GMT

Gysbert,

You answer is great & has helped, but on the left of each calculated dimension I am getting a little + symbol which I need to expand to see the values.

Can I get rid of the little '+' on each row of data so I just see the data without expanding the '+'?

Re: Need help with pivot table

Anonymous — Mon, 22 Apr 2013 07:24:12 GMT

If u don want to see the '+' sign to expand, you can select 'Always fully expanded' checkbox in the presentation tab of the chart properties.

Regards

Revathy

Re: Need help with pivot table

Mon, 22 Apr 2013 08:26:14 GMT

Hi Danseter,

Yes,You can get rid of the Little '+',do the following:

Goto>Presentation Tab>Check Always Fully Expanded option

Hope this will help you.

Regards

Deepak

Re: Need help with pivot table

Tue, 23 Apr 2013 00:33:30 GMT

Awesome! So very close now.

I think I may have 1 last question.

One of my quantity columns has 6 decimals for each column of values so it looks

6.000000

13.000000

9.0000000

and so on.

I can't find anywhere in the properties to adjust that. The other quantity columns don't display like that. Can I change the column to get rid of the decimals?

Thanks so much so far!

Re: Need help with pivot table

Anonymous — Tue, 23 Apr 2013 06:08:11 GMT

In the Number Tab, we can change the Number format settings for particular field in the expression.

If u don want decimal then change to Number format.

regards

Revathy.

Re: Need help with pivot table

Tue, 23 Apr 2013 06:36:40 GMT

But I don't get my column appearing in the Number tab, under Expressions. It only has my monthly SalesQuantity. None of the other columns display here.

Re: Need help with pivot table

Gysbert_Wassenaar — Tue, 23 Apr 2013 09:43:40 GMT

Try something like =num(aggr(sum([Current Stock Qty]),product),'#,###')

You may have to fool around a bit with the format string of the num function to get exactly what you want.

Re: Need help with pivot table

Tue, 23 Apr 2013 23:59:59 GMT

Excellent!

So now I have the display of the pivot table looking good.

Now when I select my period ranges, I want to only see the data change for the Sales Quantity's under each month, whilst still keeping the Current Stock Qty as it is now.

Unfortunately, it seems the extra 3 columns i have added using the AGGR function as suggested are still being pivoted because each time I change the month selection, those columns lose their data.

Re: Need help with pivot table

Gysbert_Wassenaar — Wed, 24 Apr 2013 11:22:41 GMT

If you want to ignore selections in every field try:

=num(aggr(sum({1}[Current Stock Qty]),product),'#,###')

If you want to ignore selections in specific fields (e.g. MyField1 and MyField2) try:

=num(aggr(sum({<MyField=,MyField2= >}[Current Stock Qty]),product),'#,###')

Re: Need help with pivot table

Mon, 29 Apr 2013 03:46:24 GMT

That's awesome Gysbert.

That pivot works just the way I intended. Super!