Need help with pivot table

[#1]

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

[Gysbert_Wassenaar]

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

[#3]

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 '+'?

[Anonymous]

Hi

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

[#5]

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

[#6]

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!

[Anonymous]

Hi

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.

[#8]

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.

[Gysbert_Wassenaar]

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.

[#10]

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.