topic Re: How do I add a Total Budget column while having monthly Revenue Columns? in QlikView

How do I add a Total Budget column while having monthly Revenue Columns?

Tue, 30 Oct 2012 09:59:02 GMT

I have Product and MonthYear as Dimensions and Sum of Actual Revenue as an expression.

My Pivot Table shows the MonthYear along the top with a value for each product.

If I add a Budget Figure I get the Budget for each month but I only want the Total Budget to appear

What I want is:

Product Jan Revenue Feb Revenue...etc ........Total Revenue Total Budget

At the moment if I add the Budget to the expression I get:

Product Jan Revenue Jan Budget Feb Revenue Feb Budget etc..... Total Revenue Total Budget

Any ideas anyone?

Re: How do I add a Total Budget column while having monthly Revenue Columns?

Gysbert_Wassenaar — Tue, 30 Oct 2012 10:09:40 GMT

You can add Total Budget as a calculated dimension. That way it will be treated as a dimension and you can put it after Product.

Re: How do I add a Total Budget column while having monthly Revenue Columns?

Tue, 30 Oct 2012 10:14:02 GMT

Thanks for replying.

Can you give me an example because if I move the expression to a Calculated Dimension I get an error in Calculated Dimension

Re: How do I add a Total Budget column while having monthly Revenue Columns?

Tue, 30 Oct 2012 10:14:10 GMT

Thanks for replying.

Can you give me an example because if I move the expression to a Calculated Dimension I get an error in Calculated Dimension

Re: How do I add a Total Budget column while having monthly Revenue Columns?

Gysbert_Wassenaar — Tue, 30 Oct 2012 10:21:39 GMT

See attached qvw

Re: How do I add a Total Budget column while having monthly Revenue Columns?

Tue, 30 Oct 2012 11:43:54 GMT

Hi - I've got it working now thank you - unfortunately if there is no Revenue but there is a Budget number against that Product, the Product does not appear as there is no expression.

The moral could be that if you're not going to make quota don't sell anything as it won't appear in the table however the salesperson will get fired and I probably will too!!

Any work around that you can think of?

Re: How do I add a Total Budget column while having monthly Revenue Columns?

Tue, 30 Oct 2012 11:44:18 GMT

Hi - I've got it working now thank you - unfortunately if there is no Revenue but there is a Budget number against that Product, the Product does not appear as there is no expression.

The moral could be that if you're not going to make quota don't sell anything as it won't appear in the table however the salesperson will get fired and I probably will too!!

Any work around that you can think of?

Re: How do I add a Total Budget column while having monthly Revenue Columns?

Wed, 31 Oct 2012 11:21:20 GMT

Hi - here is a sample of what I've got and what I'm trying to achieve. Explanation is in text boxes

Re: How do I add a Total Budget column while having monthly Revenue Columns?

Gysbert_Wassenaar — Wed, 31 Oct 2012 11:45:34 GMT

I can get you one step closer. Uncheck the Suppress Zero-Values option for dimension Product on the Presentation tab of the properties screen. That way products with 0 revenue will be shown too.

Re: How do I add a Total Budget column while having monthly Revenue Columns?

Wed, 31 Oct 2012 13:26:41 GMT

Hi Gysbert

Presumably Products with 0 Revenue and 0 Budget will come through too. That would not work as there are hundreds of Products unforunately

Re: How do I add a Total Budget column while having monthly Revenue Columns?

Gysbert_Wassenaar — Wed, 31 Oct 2012 13:42:36 GMT

If the Revenue is actually 0 then yes it will be shown. But if there is no data, like for product D in June 2012, then it won't be shown.

Re: How do I add a Total Budget column while having monthly Revenue Columns?

Thu, 01 Nov 2012 10:07:43 GMT

Hi Gysbert

Thanks for your help on this but I don't think I can work it this way.

I suppose another alternative is to set each individual column up using set analysis. i.e. 1st Column = Jan Actual, 2nd Column = Feb Actual etc....... then Total Actual and finally Total Budget

It loses flexibility but I guess it should work. What's your view?

Re: How do I add a Total Budget column while having monthly Revenue Columns?

Clever_Anjos — Thu, 01 Nov 2012 10:11:46 GMT

Try

if(ColumnNo()=0,sum(Budget), sum(Revenue))

as your expression

Re: How do I add a Total Budget column while having monthly Revenue Columns?

Thu, 01 Nov 2012 10:38:59 GMT

Not sure I've got that one working right - can you please show me how you are thinking of using it in the above Sample Date.qvw

Re: How do I add a Total Budget column while having monthly Revenue Columns?

Thu, 01 Nov 2012 10:39:04 GMT

Not sure I've got that one working right - can you please show me how you are thinking of using it in the above Sample Date.qvw