topic Re: Formula to Divide by Total Amount in QlikView

Formula to Divide by Total Amount

Anonymous — Mon, 11 Aug 2014 14:47:35 GMT

In the table above the column labeled 7Day% is using a formula (=FirstSortedValue(iHeadMort,Mort_dt) / SUM(Head_Start)) but I need it to divide by the total Head_Start (Head Placed) of 20,944 instead of the separate values of 19,250 and 1,694. Any ideas for how to divide each column by the total amount or at least get my total line to show the correct amount?

Re: Formula to Divide by Total Amount

joshabbott — Mon, 11 Aug 2014 14:50:27 GMT

Did you try:

sum(TOTAL [Head Placed])

Re: Formula to Divide by Total Amount

Colin-Albert — Mon, 11 Aug 2014 14:50:30 GMT

try =FirstSortedValue(iHeadMort,Mort_dt) / SUM(total Head_Start))

Re: Formula to Divide by Total Amount

Anonymous — Mon, 11 Aug 2014 14:59:04 GMT

Colin,

That works great when I only have this Project selected, but when I deselect it seems to recalculate using all the amounts.

Re: Formula to Divide by Total Amount

Colin-Albert — Mon, 11 Aug 2014 15:06:46 GMT

If you want the total to be summed by the Project data then use

=FirstSortedValue(iHeadMort,Mort_dt) / SUM(total <Project> Head_Start))

where Project is the fieldname for the Project data. <[Group Cd]>

Re: Formula to Divide by Total Amount

Anonymous — Mon, 11 Aug 2014 15:19:51 GMT

That makes perfect sense.

I have put this in:

=FirstSortedValue(iHeadMort,Mort_dt) / SUM(total Project_# Head_Start)

It is not returning a value, so am I missing something between the Project_# and Head_Start? Thanks for your patience.

Re: Formula to Divide by Total Amount

Colin-Albert — Mon, 11 Aug 2014 15:24:56 GMT

You need to add <>

=FirstSortedValue(iHeadMort,Mort_dt) / SUM(total <Project_#> Head_Start)

Re: Formula to Divide by Total Amount

Anonymous — Mon, 11 Aug 2014 15:46:38 GMT

That's it! thanks Colin, your the man!