Discussion Board for collaboration related to QlikView App Development.
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.
Hi,
Check with this
sum(Extprice) / sum(TOTAL<Month> {<Concept={Sales}>} Extprice)
Celambarasan
Hi,
Check with this
sum(Extprice) / sum(TOTAL<Month> {<Concept={Sales}>} Extprice)
Celambarasan
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
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
Thanks,
I was so closed with the aggr function, that I never thought read the sum function.
Thanks again
karim