nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to find sum of unique values

vivekpv10

In my report i have one column with name expense.It contain values like this.
120
120
130
130
140
140
140
140
150
150

I want to get sum of unique values in this column(ie:120+130+140+150).how can i do that.Is it possible to do with report varaibles??

Find more posts tagged with

Comments

Hans_vd

My first thought:
- create a grouping on the field that contains these values
- add a COUNT aggregation to the group
- add an expression to the table that does (value / count aggregation
- add a SUM aggregation on the expression
- make everything that you don't want to see invisible

kclark

You could also do this in your query by suming the distinct values. It would look something like this 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>select sum( distinct CLASSICMODELS.EXPENSE.SOMEROW ) as summedExpense
from CLASSICMODELS.EXPENSE</pre>

Hans_vd

<blockquote class='ipsBlockquote' data-author="'kclark'" data-cid="114919" data-time="1362765324" data-date="08 March 2013 - 10:55 AM">
You could also do this in your query by suming the distinct values. It would look something like this 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>select sum( distinct CLASSICMODELS.EXPENSE.SOMEROW ) as summedExpense
from CLASSICMODELS.EXPENSE</pre></blockquote>
 
That would be a lot easier :-)

vivekpv10

In onCreate event of corresponding item,i have added below expression,but it is not working..Hewre sV1,sV2&sV3 are report varaibles.Logic is value is same as previous,skip that value. 
vars["sV3"]=new Number(this.getValue()); 
if((vars["sV1"]-vars["sV3"])!=0) 
{ 
vars["sV1"]= new Number(this.getValue()); 
} 
else 
{ 
vars["sV1"]=0; 
} 
vars["sV2"]=vars["sV2"]+vars["sV1"]; 
 
vars["sV2"] will be using in group footer for showing sum of unique items,also it will reset to zero on group header onCreate event

Hans_vd

Do the variables sV1, sV2 and sV3 have default values?
If not, enter 0 as default value for all of them.

vivekpv10

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="114955" data-time="1362985914" data-date="11 March 2013 - 12:11 AM">
Do the variables sV1, sV2 and sV3 have default values? 
If not, enter 0 as default value for all of them. </blockquote>
Yes..already it is 0..Also all these varaibale can hold decimal values

Hans_vd

I copy/pasted your script into a report and it works just like that.
Not sure why it is not working for you.

Do you have any other scripts in your report that use these variables? -> in the outline view, select scripts to see them all
How exactly does the group header onCreate script?