top 5 sales base on selection

[prma7799]

Hi All,

SUM({$<FinancialYear=,FiscalYear={$(=max(FiscalYear))},MonthName=,Quarter=
,[ Date]={">=$(=YearStart(max({$<FiscalYear={$(=max(FiscalYear))}>}

[Date]),0,4))<=$(=max({$<FiscalYear={$(=max(FiscalYear))}>}[ Date]))"}
>}[Sales])

I have above expression and i want show top 5 state ytd sale base on the selection need to show top 5 sale individually in different txt object.

[HirisH_V7]

Hi you can do like this,

In your table,

Dimension:-

1. YTD
2. =if(aggr(rank(

SUM({$<FinancialYear=,FiscalYear={$(=max(FiscalYear))},MonthName=,Quarter=
,[ Date]={">=$(=YearStart(max({$<FiscalYear={$(=max(FiscalYear))}>}

[Date]),0,4))<=$(=max({$<FiscalYear={$(=max(FiscalYear))}>}[ Date]))"}
>}[Sales])),State)<=5,State)

Expression:

SUM({$<FinancialYear=,FiscalYear={$(=max(FiscalYear))},MonthName=,Quarter=
,[ Date]={">=$(=YearStart(max({$<FiscalYear={$(=max(FiscalYear))}>}

[Date]),0,4))<=$(=max({$<FiscalYear={$(=max(FiscalYear))}>}[ Date]))"}
>}[Sales])

Another way in text box,

='State and Their respective Sales                     ' &Concat(if(aggr(rank(

SUM({$<FinancialYear=,FiscalYear={$(=max(FiscalYear))},MonthName=,Quarter=
,[ Date]={">=$(=YearStart(max({$<FiscalYear={$(=max(FiscalYear))}>}

[Date]),0,4))<=$(=max({$<FiscalYear={$(=max(FiscalYear))}>}[ Date]))"}
>}[Sales])),State)<=5,

Aggr(State&CHR(9)&MONEY(SUM(Sales),'#,##0'),State)),', '&CHR(10),-Aggr(SUM(Sales),State))

Hope this Helps,

PFA,

Hirish

[Anonymous]

You can do it in 3 way:

1) Already suggested by Hirish

2) Properties -> Dimension Limit ->Tick(Which Restrict option) -> Show Largest 5 values

3) Properties -> Presentation -> Chart Scrolling -> Enable X Axis Scroll Bar -> When Number of Item Exceeds=5

    And Make sure your expression either in ascending or descending

[prma7799]

Hi All,

I want this values in Text object.

If user select 2014-2015 then respect to year in text object it will show ytd sales for state .

In below text object I want ytd sales for top 5 states .

suppose first is Maharashtra then first object should display maha... state ytd sale.

Thanks

[Anonymous]

Did you check these?

Set Analysis for certain Point in Time

Set Analysis for Rolling Periods

[HirisH_V7]

Hi,

Do check my modified post above.

-Hirish

[HirisH_V7 · Accepted solution]

Hi ,

Do check this too,

HTH,

Hirish

[prma7799]

Hi Hirish,

You solution is working fine but I dont want state name from that txt object only want sales.

Thanks

[HirisH_V7]

Hi ,

Change of expression in text Box like this,

='Customer and Their respective Sales             2      

' &Concat(if(aggr(rank(sum(Sales)),Customer)=2,Aggr(MONEY(SUM(Sales),'#,##0'),Customer)),', '&CHR(10),-Aggr(SUM(Sales),Customer))

-Hirish