topic Re: % for each expense respect to the monthly sale value in QlikView

% for each expense respect to the monthly sale value

Thu, 14 Jun 2012 19:08:38 GMT

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.

Re: % for each expense respect to the monthly sale value

CELAMBARASAN — Thu, 14 Jun 2012 19:35:10 GMT

Hi,

Check with this

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

Celambarasan

Re: % for each expense respect to the monthly sale value

Thu, 14 Jun 2012 20:36:21 GMT

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

Re: % for each expense respect to the monthly sale value

CELAMBARASAN — Fri, 15 Jun 2012 17:29:16 GMT

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

Re: % for each expense respect to the monthly sale value

Fri, 15 Jun 2012 20:14:53 GMT

Thanks,

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

Thanks again

karim