Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I used below given expression in calculated dimension to show only top 3 brands
=if(aggr(rank(sum({<AsOfYear={'$(=max(AsOfYear))'}>}Sales)),Brand)<=3,Brand)
and used this expression in expression tab
FirstSortedValue({<AsOfMonthNr = {$(=Max({<AsOfYear = {$(=Max(AsOfYear))}>}AsOfMonthNr))},Brand, PeriodType = {'CYMONTH'}, AsOfYear = {"$(='<=' & Max(AsOfYear))"}, AsOfMonth>}
Aggr(Sum({<Brand, PeriodType = {'CYMONTH'}, AsOfYear, AsOfMonth>}Sales), AsOfPeriod, Brand),
-Aggr(Only({<Brand, PeriodType = {'CYMONTH'}, AsOfYear, AsOfMonth>}AsOfPeriod), AsOfPeriod, Brand))
/
Sum(TOTAL <AsOfPeriod>Aggr(FirstSortedValue({<AsOfMonthNr = {$(=Max({<AsOfYear = {$(=Max(AsOfYear))}>}AsOfMonthNr))},Brand, PeriodType = {'CYMONTH'}, AsOfYear = {"$(='<=' & Max(AsOfYear))"}, AsOfMonth>}
Aggr(Sum({<Brand, PeriodType = {'CYMONTH'}, AsOfYear, AsOfMonth>}Sales), AsOfPeriod, Brand, AsOfYear),
-Aggr(Only({<Brand=, PeriodType = {'CYMONTH'}, AsOfYear, AsOfMonth>}AsOfPeriod), AsOfPeriod, Brand, AsOfYear)), AsOfYear, Brand)))
This expression is work fine the selected year & month but not for previous for year & month and
This expression works fine when the brand is below 3 or equal to 3 for the selected period.... I mean, only sold brand for the selected period...
There are some other brand also sold in selected period ... The brand share totals 100% when i am trying to show only top 3...
Required output refer the screen shot consider only 2016 values
Attached the sample data
Try this
(FirstSortedValue({<AsOfMonthNr = {$(=Max({<AsOfYear = {$(=Max(AsOfYear))}>}AsOfMonthNr))},
Brand = {"=Rank(FirstSortedValue({<AsOfMonthNr = {$(=Max({<AsOfYear = {$(=Max(AsOfYear))}>}AsOfMonthNr))}, Brand, PeriodType = {'CYMONTH'}, AsOfYear = {'$(=Max(AsOfYear))'}, AsOfMonth>}
Aggr(Sum({<Brand, PeriodType = {'CYMONTH'}, AsOfYear, AsOfMonth>}Sales), AsOfPeriod, Brand),
-Aggr(Only({<Brand, PeriodType = {'CYMONTH'}, AsOfYear, AsOfMonth>}AsOfPeriod), AsOfPeriod, Brand))) <= 3"},
PeriodType = {'CYMONTH'}, AsOfYear = {"$(='<=' & Max(AsOfYear))"}, AsOfMonth>}
Aggr(Sum({<Brand, PeriodType = {'CYMONTH'}, AsOfYear, AsOfMonth>}Sales), AsOfPeriod, Brand),
-Aggr(Only({<Brand, PeriodType = {'CYMONTH'}, AsOfYear, AsOfMonth>}AsOfPeriod), AsOfPeriod, Brand))
/
Sum({<AsOfMonthNr = {$(=Max({<AsOfYear = {$(=Max(AsOfYear))}>}AsOfMonthNr))},Brand , PeriodType = {'CYMONTH'}, AsOfYear = {"$(='<=' & Max(AsOfYear))"}, AsOfMonth>}TOTAL <AsOfYear>Aggr(FirstSortedValue({<AsOfMonthNr = {$(=Max({<AsOfYear = {$(=Max(AsOfYear))}>}AsOfMonthNr))},Brand , PeriodType = {'CYMONTH'}, AsOfYear = {"$(='<=' & Max(AsOfYear))"}, AsOfMonth>}
Aggr(Sum({<Brand, PeriodType = {'CYMONTH'}, AsOfYear, AsOfMonth>}Sales), AsOfPeriod, Brand, AsOfYear),
-Aggr(Only({<Brand=, PeriodType = {'CYMONTH'}, AsOfYear, AsOfMonth>}AsOfPeriod), AsOfPeriod, Brand, AsOfYear)), AsOfYear, Brand)))*100
Hi All
I made a correction in calculated dimension my selected year & month show correct values but my previous year of same month not show correct values
=if(aggr(rank(
sum({<AsOfPeriod ={"$(='>=' & Date(AddMonths(Max(AsOfPeriod), -11), 'MMM-YY') & '<=' & Date(Max(AsOfPeriod), 'MMM-YY'))"},PeriodType={'CYMONTH'}>}Sales)),Brand)<=3,Brand)
Can any one help me......
Seems you have done aggr before rank only. May be this?
If(Aggr(Rank(sum({<AsOfPeriod ={">=$(=Date(AddMonths(Max(AsOfPeriod), -11), 'MMM-YY')) <= $(Date(Max(AsOfPeriod), 'MMM-YY'))"},PeriodType={'CYMONTH'}>}Sales))<=3, Brand), Brand)
No Brother ... its not working.... same values are showing
Get output
Required Ouput values are
Anil...
can help what is the error in this expression
Brand = {"=rank(sum({<AsOfPeriod ={"$(='>=' & Date(AddMonths(Max(AsOfPeriod), -11), 'MMM-YY') & '<=' & Date(Max(AsOfPeriod), 'MMM-YY'))"}>}Sales)) <= 3"}
when i tired to insert it with main one it has some error.....
Try this
(FirstSortedValue({<AsOfMonthNr = {$(=Max({<AsOfYear = {$(=Max(AsOfYear))}>}AsOfMonthNr))},
Brand = {"=Rank(FirstSortedValue({<AsOfMonthNr = {$(=Max({<AsOfYear = {$(=Max(AsOfYear))}>}AsOfMonthNr))}, Brand, PeriodType = {'CYMONTH'}, AsOfYear = {'$(=Max(AsOfYear))'}, AsOfMonth>}
Aggr(Sum({<Brand, PeriodType = {'CYMONTH'}, AsOfYear, AsOfMonth>}Sales), AsOfPeriod, Brand),
-Aggr(Only({<Brand, PeriodType = {'CYMONTH'}, AsOfYear, AsOfMonth>}AsOfPeriod), AsOfPeriod, Brand))) <= 3"},
PeriodType = {'CYMONTH'}, AsOfYear = {"$(='<=' & Max(AsOfYear))"}, AsOfMonth>}
Aggr(Sum({<Brand, PeriodType = {'CYMONTH'}, AsOfYear, AsOfMonth>}Sales), AsOfPeriod, Brand),
-Aggr(Only({<Brand, PeriodType = {'CYMONTH'}, AsOfYear, AsOfMonth>}AsOfPeriod), AsOfPeriod, Brand))
/
Sum({<AsOfMonthNr = {$(=Max({<AsOfYear = {$(=Max(AsOfYear))}>}AsOfMonthNr))},Brand , PeriodType = {'CYMONTH'}, AsOfYear = {"$(='<=' & Max(AsOfYear))"}, AsOfMonth>}TOTAL <AsOfYear>Aggr(FirstSortedValue({<AsOfMonthNr = {$(=Max({<AsOfYear = {$(=Max(AsOfYear))}>}AsOfMonthNr))},Brand , PeriodType = {'CYMONTH'}, AsOfYear = {"$(='<=' & Max(AsOfYear))"}, AsOfMonth>}
Aggr(Sum({<Brand, PeriodType = {'CYMONTH'}, AsOfYear, AsOfMonth>}Sales), AsOfPeriod, Brand, AsOfYear),
-Aggr(Only({<Brand=, PeriodType = {'CYMONTH'}, AsOfYear, AsOfMonth>}AsOfPeriod), AsOfPeriod, Brand, AsOfYear)), AsOfYear, Brand)))*100
yeah brother.... its working fine ....thank you very much.....
....
No problem