Grandsummary

[joaosales]

Hi, is it possible to use a different operation for the grand summary?
I would like to apply a specific weight to the value in  different rows on the table while the sum for the grandSummary of a specific columns happens.
I saw that there is the formulas option, but it seems to be only for operations in the same row.


Thanks,

[paramvir]

You can use either

1) SUMPRODUCT Excel formula in grand summary row.

or

2)
use formulas to add weight to values in the same rows.

and use Excel SUM Excel formula in grand summary row.

[joaosales]

And how do I set the formula for the grand summary row?

Do I use  summaryData:  and set grandSummary true?
I'm a little lost on this, is there any example for it? other then the ones for group row summary?

[paramvir]

Excel functions are set in grandSummary with rowData.pq_fn

This is the example: https://paramquery.com/pro/demos/summary_json

and plnkr: https://plnkr.co/edit/zo6TdOvweAFvWM5JLasV?p=preview

[joaosales]

Hi, still having some problems with the summary.
I wanted to set it like this

summaryData:calculateTotals(grid, start, finish);

where in the grid i would access the table data, start and finish would help create the dataIndx for the columns and use some extra variables saved in the js to calculate the totals with the weight.
is it possible to work with the summaryData field like this?

[paramvir]

It's lot easier to do it with Excel ready to use functions, however you may also compute summaryData imperatively ( manually with javascript code ).

Please share a plnkr or jsfiddle if you face any issues.

[joaosales]

I'm trying with custom aggregates now, but I would still need some extra info, like the rows that are used to find out which weight to apply, the functions get an array and the column, is there a way to know the rows too?
i cant simple use the position on the array because i have groups on the table.

[paramvir]

Custom aggregates ( different from Excel functions ) in pq.aggregate namespace can operate on the values of the current column only.

Since you want to apply weight to the values, you would also need js formulas ( https://paramquery.com/pro/api#option-formulas )

price               weight               val
10                   2                       20
20                   3                       60
                                               
Sum                                         80 (column.summary.type = 'sum')

Code: [Select]

formulas: [
    //val is dataIndx of the column.
    ["val", function( rd ){
        //dependent upon price and weight
        return rd.price * rd.weight
    }]
];


[joaosales]

I guess instead of describing how I'm trying to solve my problem, Ill describe the problem, maybe it'll work out better.

I'm using the grid to collect financial data, each row has a different currency assigned to it. and I have a conversion rate for each column.

Basically, this means that each cell has a different weight to it and this weight is stored in an external data source, in memory.

what I want to do for my aggregate formula, is to lookup in this external source the weight for the current cell and multiply it (weight[row][colum] * cellValue). The calculated value needs to be displayed only in the summaries, which uses the system default currency.

What is the best way to do this?

Thanks for all the help!

[paramvir]

My previous answer still applies with a slight change in formula since weight is stored in an external data source

Code: [Select]

formulas: [
    //val is dataIndx of the column.
    ["val", function( rd ){
        var ri = this.getRowIndx({rowData: rd}).rowIndx;
        //dependent upon price and weight
        return weight[ ri ][ colum ] * rd.price;
    }]
];


[joaosales]

Hi,

I still don't think of a way to make it work with aggregate and formulas together, since the weight is only applied to the summary row
and i dont have fixed dataIndx for the columns, they are mapped depending on year/month, and i can change the view of the table using
a button/filter, to show me a different year.

And example of the data i have is like this, the default currency is Euros, so its values are 1 for all months. And the other
currencies are based on it. On the Grid table, each row can have a different currency, and the second column, specifies which currency
by name, allowing me to match with the name of a currency in the first column of the currencies table.
So the weight is only applied when I want to calculate the Summary for the grid table.



Currencies Table
   oct    nov    dec   
eur   1      1     1
usd  0,8    0,8   0,8   
dkk  0,2    0,2   0,2



Grid Table

                      2017
          oct   nov  dec     total
item1 eur  10   15   20       45
item2 dkk  40   40   40      120
item3 usd   5    5    5        15
Total EUR  22   27   32       81    < - only row where the weight is applied (and calculate the sum of each element in the column*weight)
         
         
From what i could understand, formulas are mapped by the dataIndx of a column,and if I use this i wouldnt be able to load the values.
And when in a formula i only have the row info, the same way as in a aggregate function i only have the column info. If there is a way, how could i combine
both? would really like if you have a working example on this.

Later I will try to create a smaller version of my grid on plnkr  and post here, to help the understanding.

Thanks so much for the help so far, and sorry for all the trouble.

[paramvir]

Here is a working example for your reference.

https://plnkr.co/edit/IBJuF4ERrR3gTC7mnASK?p=preview

ref is external data reference and summary is calculated in calculateSummary function.

[joaosales]

Thanks so much for the help. It works like a charm now!!