% for each expense respect to the monthly sale value

[#1]

Hi community,

Actually I a am working wiht a pivot table, to show sales(+) and expenses(-), the data is showin like this, and this step is fine.

                                          Jan            Feb

Sales                              10,000        12,000

Expense 1                     -     300       -     500

Expense 2                     -  1,000       -     800

Expense 3                     -  4,000       -  3,500

Total                                 4,700          7,200

but requirement is showing the % for each expense respect the month sale value.

Expenses/Monthly sale.

Concept                                     Jan                       Feb

Sales                              10,000  100.00%      12,000    100.00%

Expense 1                     -     300      3.00%     -     500        4.16%

Expense 2                     -  1,000     10.00%    -     800        6.66%

Expense 3                     -  4,000     40.00%    -  3,500      29.16%

Total                                 4,700     47.00%       7,200      60.00%

I've been trying with aggr functions but I can get the result.

The formulas that i've been using:

sum(Extprice)

sum(Extprice) / aggr(sum(TOTAL {<Concept={Sales}>} Extprice),Concept,Month) => this formula shows % respect for the total Monthly sale.

    and here is the stuff, how can I implement only for each month?, Jan, Feb, Mar, Apr....... so on.

Any suggestion to figure out the formula, it will be appreciate it.

Thanks in advance.

Karim.

[CELAMBARASAN · Accepted solution]

Hi,

     Check with this

     sum(Extprice) / sum(TOTAL<Month> {<Concept={Sales}>} Extprice)

Celambarasan

[#3]

Celambarasan, thanks a lot

Works as I need it.

But, just for learining reasons:

why the sintaxis for sum function.this is little different as usual.

Sum(TOTAL <Month>{<Concept={Sale}>} Extprice)

thanks again

karim

[CELAMBARASAN]

Hi,

If you want calculate total for particular dimension you should  use  <fieldname> after total.

For more info look the qlivkiew help file information

sum([{set_expression}][distinct][total[<fld {, fld}>]] expression)

Returns the aggregated sum of expression or field iterated over the chart dimension(s).

Examples:

sum(Sales)

sum(Price*Quantity)

sum(distinct Price)

sum(Sales)/sum(total Sales) returns the share within the selection

sum(Sales)/sum(total <Month> Sales) returns the share within the selection for each Month

sum(Sales)/sum(total <Month,Grp> Sales) returns the share within the selection for each Month and Grp

sum(Sales)/sum(total <Qtr,Month,Week> Sales) possible syntax for use with a time drill-down group

sum({1} total Sales) returns sales within the entire document

sum({BM01} Sales) returns sales within the selection defined by bookmark BM01

sum({$ <Year={2007,2008}>} Sales) returns the sales for the current selection but just for the years 2007 and 2008, that is, the same as sum(if(Year=2007 or Year=2008, Sales))

Celambarasan

[#5]

Thanks,

I was so closed with the aggr function, that I never thought read the sum function.

Thanks again

karim