Running total & Averge required to be maintained under each Telephone number

[nevilledhamsiri]

Hi,

My dear friends, As you always lend your helping hand, appreciate if you look in to the table below which is after data being loaded as to how the running total & running average being maintained for individual telephone numbers. I have applied the expressions as below to bring in the running total & average but it continuing all the way down irrespective of the different telephone numbers bill amount. As can be seen, we cannot find out the exact running total & average for individual bills. Also I need to show the correct running total & average in the "Total row" which carries nil balance as at now.

Dear all hope you may speedily response to my issue.

Best Regards

Neville

RangeSum(Above(TOTAL RangeSum(Actual),0,RowNo(TOTAL)))

RangeAvg(ABOVE(TOTAL RangeSum(Actual),0,RowNo(TOTAL)))

Sum (Actual)

sum(aggr(sum(BUDGET),Branch,Category,User,Designation,Telephone_number,Month))

sum(aggr(IF(SUM(BUDGET)-sum(Actual)<0,BUDGET-Actual,0),Branch,Category,User,Designation,Telephone_number,Month))

[nevilledhamsiri]

Please help me on this!

Regards

Neville

[sunny_talwar]

What is the expected output here?

[nevilledhamsiri]

Dear Sunny,

As can be seen when I calculated the running total & averages for different telephone numbers (which you can see in the table attached), it produces results not in relation to the individual telephone numbers but growing the figures over all telephone numbers. What I need is......

To calcula running total & averages for each telephone numbers separately. In other wards, once it is calculated for one number it should be stopped there with correct figure & for the next number it should start in fresh & so on. Here the figures I need to show for different telephone numbers separately.

Actual telephone bills I have loaded from January to July & I need to calculated running total & average seperately for different telephone numbers.

I think sunny you have such an amazing skills to resolve this & help me!

Thanks

[sunny_talwar]

Try without TOTAL

RangeSum(Above(RangeSum(Actual),0,RowNo()))

RangeAvg(Above(RangeSum(Actual),0,RowNo()))

[nevilledhamsiri]

Dear Sunny!

Your expression worked well. Below being the out put.If you don't mind will you be able to help me to bring in the running total & averages in the total row which is now carrying nil balance if so this table will looks fine. For 1st number, total should come as 14000/= & average as 3500/= & for the 2nd number as 21500/= & 5375/= each.

For this,, if the said expression to be modified please propose the same.

Also if I need to apply  conditional formatting like in excel such as  for any number if their Averages goes over

5000/= it should be formatted with some colors & apply a flag or something like that etc.

Neville

[sunny_talwar]

This will depend on how many dimensions you have... lets say you have 3 dimensions in your chart, then try this

If(Dimensionality() = 3, RangeSum(Above(RangeSum(Actual),0,RowNo())), Sum(Actual))

If(Dimensionality() = 3, RangeAvg(Above(RangeSum(Actual),0,RowNo())), Avg(Actual))

If there are 5 dimensions, then try this

If(Dimensionality() = 5, RangeSum(Above(RangeSum(Actual),0,RowNo())), Sum(Actual))

If(Dimensionality() = 5, RangeAvg(Above(RangeSum(Actual),0,RowNo())), Avg(Actual))

[nevilledhamsiri]

Dear Sunny,

Thanks a lot. It is great see the output. There are lot of things I can learn!

Regards

Neville

[sunny_talwar · Accepted solution]

nevilledhamsiri

I have been constantly requesting you to mark the appropriate responses as correct and helpful and not just any response. Yet again you marked your own response as correct. Think about this as a way for learning for the future visitors. When they come to this thread they will either see your response as correct or helpful, neither of which will be helpful for them because all your are doing is marking responses such as thank you or this work great as correct. Instead you should be marking those responses which actually helped you get to what you wanted.... When you do this, the future visitors will be able to come to this thread and quickly look at the relevant responses.

Please help us make this community a better place by spending some time to correct mark helpful and correct responses.

If you want, you can read more on this topic here....

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny