To display the customers in straight table

[#1]

Dear QV Experts,

Dataset: 2014-15 which retrieves the customer whose sales value for both the years.

In Expression, If Suppress when value is null unchecked, then displaying the values correctly.

But while selecting Top5 or 10, then 6th or 11th row displayed. Due to that the Expression Sum also displays incorrect.

Can anyone suggest. We need to display the null values also but while filtering based on Top5,10,20 still the null shouldnt be displayed.

Appreciate your prompt response.

Thanks,

Sasi

[sunny_talwar]

Would you be able to share a sample?

[Gysbert_Wassenaar]

Disable the Show Others option and set the Total Mode to Sum of Rows.

[#4]

Hi Sasi,

Share some sample data or example on what is required.

Regards,

Anjali Gupta

[#5]

Dear QV Experts,

Please find the example attached.

  THe input data,

CustomerName	Year	Sales
ABC	2015	1000
ABC	2014	750
ABC	2013	675
BCD	2015	1100
BCD	2014	600
BCD	2013	640
CDE	2015	1250
CDE	2014	800
CDE	2013	750
DEF	2015	900
DEF	2014	400
DEF	2013	500
EFG	2013	300

The customer EFG having Sales only in 2013.

Can anyone suggest. We need to display the null values also but while filtering based on Top5,10,20 still the null shouldnt be displayed.

In the Suppress Null Straight Table, if i select the maximum number of rows to display, i am not seeing the Customer EFG [Reason being no sales in the Year 2014,2015]

In the Suppress Not Null Straight Table, if i select only one row to be displayed, still the customer EFG with - displaying.

The requirement is.

If i select 3 rows, the top3 rows should be displayed.

If i select 5 rows, the Top5 rows including Customer EFG should be displayed in the straight table.

Appreciate your prompt response.

Thanks,

Sasi

[sunny_talwar]

Try this as your calculated dimension:

=If(Aggr(Rank(RangeSum(Sum(Sales), Sum({<CustomerName = e(CustomerName), Year>} 0))) <= vTopX, CustomerName), CustomerName)

[#7]

Thanks Sunny. Fantastic.

One more request, Can you please help me in the below calculated dimension.

=if(%Ranking_No='1', aggr(if(rank(Sum({$}{<[Year]={$(VCurrentYear)},Date={'<=$(vMaxInvoiceDate)'}>}Sales))<=vTopX,CustomerName),CustomerName),
if(%Ranking_No='2',aggr(if(rank((Sum({<[Year]={$(VCurrentYear)}>}Sales) - Sum({<[Year]={$(=(VCurrentYear)-1)},Date={'<=$(vAsOfDatePY)'}>}Sales) ))<=vTopX,CustomerName),CustomerName),
if(%Ranking_No='3',aggr(if(rank((Sum({<[Year]={$(VCurrentYear)}>}Sales)  - Sum({<[Year]={$(VCurrentYear)}>}PlannedSalesAmtYTD) ))<=vTopX,CustomerName),CustomerName),
if(%Ranking_No='4',aggr(if(rank(-(Sum({<[Year]={$(VCurrentYear)}>}Sales)  - Sum({<[Year]={$(=(VCurrentYear)-1)},Date={'<=$(vAsOfDatePY)'}>}Sales) ))<=vTopX,CustomerName),CustomerName),
if(%Ranking_No='5',aggr(if(rank(-(Sum({<[Year]={$(VCurrentYear)}>}Sales)  - Sum({<[Year]={$(VCurrentYear)}>}PlannedSalesAmtYTD) ))<=vTopX,CustomerName),CustomerName),
if(%Ranking_No='6',aggr(if(rank(num(((Sum({<[Year]={$(VCurrentYear)}>}Sales) - Sum({<[Year]={$(=(VCurrentYear)-1)},Date={'<=$(vAsOfDatePY)'}>}Sales))
/Sum({<[Year]={$(=(VCurrentYear)-1)},Date={'<=$(vAsOfDatePY)'}>}Sales)),'#,##0%'))<=vTopX,CustomerName),CustomerName)
))))))

Thanks a lot!!!

[sunny_talwar]

Hi Sasi -

I suggest closing this thread down and creating another thread to start a discussion for this calculated dimension. You need to tell us what isn't working and probably share a sample like you just did for the issue we resolved.

Best,

Sunny