Need help in aggr function

[sunil2288]

Hi All,

Actually i have a problem in straight table chart.

a>

In this app when i enter the variable as 7 , it is showing the top 6 sum(sales), but in the total row it is showing the total of all the rows (86000) instead of 66000.

I have attched the application, as it is having inline data all can reload this app.

b>

I have also a second requirement i need the % Contribution of Top N "Sum(Sales)" against the Sum(Total sales).

I have tried the Set expression but it is not working

sum({$<Dept={"aggr(if(rank(sum(Sales))<=$(V1),Dept),Name,Dept,ID)"}>}Sales) / Sum(TOTAL Sales)

here i need the %Contribution of Sum(Sales) of Top V1 Dept against Sum(Total Sales)

Please help me solving this and let me know if any queries regarding understanding the requirement.

PFA application.

Regards

Sunil

[sunil2288]

Hi Celam,

Thanks for your valuable effort,

I tried with the expression you have suggested :

Sum({<ID={"=Rank(Sum(Sales))<4"}>}Sales)

but with this expression the total value showing wrong.

Screenshot attached .

anyways thanks for your effort.

Regards

Sunil

[sunil2288]

Hi Celam,

Thanks for your valuable effort,

I tried with the expression you have suggested :

Sum({<ID={"=Rank(Sum(Sales))<4"}>}Sales)

but with this expression the total value showing wrong.

Screenshot attached .

anyways thanks for your effort.

Regards

Sunil

[CELAMBARASAN]

Hi,

     Did you checked with the another solution?

Celambarasan

[sunil2288]

Hi Celam,

You are absolutely right if i will add a Key, all the problem will get resolved, but in the real case scenario what i am facing there is no scope of changing the Data Model, as becoz this expression has to be implemented on 8 dimensions which also includes Calculated dimensions. and the data model can't be changed.

So it has to be get resolved in the frontend.

Thanks for your quick reply..

Thanks

Sunil

[CELAMBARASAN]

Hi,

     Changing data model is possible.Having a unique field in a table will be a good practice.Can you post the script how you created the table?.let me try to change it

Celambarasan

[sunil2288]

Hi Celam,

I agree it is good practice to have Unique field in a table. In this case the dimensions are from different table and expressions are from different table (Not a single expression too many expression has to be implemented like this). Sorry to say but again i am repeating there is no scope of changing the data model and reload again.

Anyway i need to do in the frontend.

Thanks for your reply.

Regards
Sunil

[perumal_41]

Hi Sunil ,

Try this expression

Sum(Total <ID,Name,Dept> Aggr(Distinct if(rank(sum(Sales))<=V1,sum(Sales)),ID))

Regards

Perumal A

[CELAMBARASAN]

Hi,

     Then try this expression

     =Sum(aggr(if(Rank(Total Sum(Sales))<=V1,Sum(Sales)),Name,ID,Dept))

Celambarasan

[sunil2288 · Accepted solution]

Hi Celam and Perumal,

Thanks for your response. The expression given by Celam is working correctly. How much i can able to do it in my complicated case has to be watched.

Sum(aggr(if(Rank(Total Sum(Sales))<=V1,Sum(Sales)),Name,ID,Dept))

Thanks

Sunil.