Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi all,
I wanted to show total line in line chart my chart dimension and measures as follows
Thanks
Vikas
dim : =Pick(Match($(vSalesView),1,2,3),MonthName,Quarter, if(FinancialYear >='2012-13',FinancialYear))
& Company
measure :
Pick(Match($(vMeasure),5,2,3,4,1,6),
// Sales Volume
( if($(vSalesView)=1 or $(vSalesView)=2,if((
SUM({$<FinancialYear=,Quarter=,FiscalYear={$(=max(FiscalYear))},
[Posting Date]={">=$(=YearStart(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]),0,4))<=$(=max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]))"}>}Y2Sale)/$(vDivide))>0,
num(SUM({$<FinancialYear=,Quarter=,FiscalYear={$(=max(FiscalYear))},
[Posting Date]={">=$(=YearStart(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]),0,4))<=$(=max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]))"}>}Y2Sale)/$(vDivide),'##,###.#')
,''), num(sum({$<FinancialYear=>} Y2Sale)/$(vDivide),'##,###.#') )) ///num(sum(Y2Sale)/$(vDivide),'###.#')
,// Mop-UP Sale
if($(vSalesView)=1 or $(vSalesView)=2,
num(
(
Sum({<FiscalYear={$(=max(FiscalYear))} >}SALES_IN_KL_CURRENT)
-
SUM({$<FinancialYear=,Quarter=,FiscalYear={$(=max(FiscalYear))},
[Posting Date]={">=$(=YearStart(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]),0,4))<=$(=max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]))"}>}
SALES_IN_KL_LAST_YEAR)
//sum({<FiscalYear={$(=max(FiscalYear)-1)},YEAR_MONTH=p(YEAR_MONTH_PREVIOUS)>}SALES_IN_KL_LAST_YEAR)
)/$(vDivide)
,'##,###.#')
,
if(GetSelectedCount(FinancialYear)=0,
num((Sum(SALES_IN_KL_CURRENT)-sum(SALES_IN_KL_LAST_YEAR))/$(vDivide),'#,###.#'),
num(
(sum({$ <FinancialYear=>} SALES_IN_KL_CURRENT-SALES_IN_KL_LAST_YEAR)/$(vDivide)
)
,'##,###.#'))
)
, //Market Share
//Option 3 Market Share
if($(vSalesView)=1,
num(((Sum({<FiscalYear={$(=max(FiscalYear))}>}Y2Sale)/aggr(nodistinct Sum ({<FiscalYear={$(=max(FiscalYear))},COMP=>}Y2Sale),MonthName)))*100,'##.##'),
if($(vSalesView)=2,
//num(((Sum(Y2Sale)/aggr(nodistinct Sum ({<COMP=>}Y2Sale),Quarter))),'#,##0.0%'),
num(((Sum({<FiscalYear={$(=max(FiscalYear))}>}Y2Sale)/aggr(nodistinct Sum ({<FiscalYear={$(=max(FiscalYear))},COMP=>}Y2Sale),Quarter)))*100,'##.##'),
if($(vSalesView)=3,
num(((Sum(Y2Sale)/aggr(nodistinct Sum ({<COMP=>}Y2Sale),FinancialYear) ))*100 ,'##.##')
)))
// num(Sum({<FinancialYear=,Quarter=,MonthName=,Year={">=$(=Max(Year)-6)<=$(=Max(Year))"}>} Y2Sale )/$(vTotY2Sale),'#,##0.0%') )))
,
// Market Share (+/-)
if($(vSalesView)=1,
// Monthly
num(((Sum({<FiscalYear={$(=max(FiscalYear))}>}Y2Sale)
/aggr(nodistinct Sum ({<FiscalYear={$(=max(FiscalYear))},COMP=>}Y2Sale),MonthName)))*100
-
((SUM({$< FiscalYear={$(=max(FiscalYear)-1)},[Posting Date]={'<=$(vLMaxDate)'},FinancialYear=
,MonthName=>}Y2Sale)
/aggr(nodistinct
SUM({$< FiscalYear={$(=max(FiscalYear)-1)},[Posting Date]={'<=$(vLMaxDate)'},FinancialYear=
,MonthName=,COMP=>}Y2Sale)
,MonthName)))*100,'##.##')
,
if($(vSalesView)=2,
// Quarterly
num(((Sum({<FiscalYear={$(=max(FiscalYear))}>}Y2Sale)
/aggr(nodistinct Sum ({<FiscalYear={$(=max(FiscalYear))},COMP=>}Y2Sale),Quarter)))*100
-
((
SUM({$< FiscalYear={$(=max(FiscalYear)-1)},[Posting Date]={'<=$(vLMaxDate)'},FinancialYear=
,Quarter=>}Y2Sale)
/aggr(nodistinct
SUM({$< FiscalYear={$(=max(FiscalYear)-1)},[Posting Date]={'<=$(vLMaxDate)'},FinancialYear=
,Quarter=,COMP=>}Y2Sale),Quarter)))*100,'##.##')
,
//Yearly
if($(vSalesView)=3 , num(
((Sum(SALES_IN_KL_CURRENT)/aggr(nodistinct Sum( {<COMP=>} SALES_IN_KL_CURRENT),FiscalYear))
- (Sum(SALES_IN_KL_LAST_YEAR)/aggr(nodistinct Sum( {<COMP=>} SALES_IN_KL_LAST_YEAR),FiscalYear)))*100
,'##.##')
)))
,
// Growth %
if($(vSalesView)=1 or $(vSalesView)=2,
num((((
SUM({$<FinancialYear=,Quarter=,FiscalYear={$(=max(FiscalYear))},
[Posting Date]={">=$(=YearStart(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]),0,4))<=$(=max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]))"}>}
Y2Sale)
)
-
Sum({$<FinancialYear=,FiscalYear={$(=max(FiscalYear)-1)},[Posting Date]={">=$(=YearStart(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]),-1,4))<=$(=addmonths(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]),-12))"}>}
Y2Sale)
)
/Sum({$<FinancialYear=,FiscalYear={$(=max(FiscalYear)-1)},[Posting Date]={">=$(=YearStart(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]),-1,4))<=$(=addmonths(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]),-12))"}>} Y2Sale))*100,
'##.##')
,
num(
((sum({<FinancialYear=>} SALES_IN_KL_CURRENT)-sum({<FinancialYear=>} SALES_IN_KL_LAST_YEAR))/sum({<FinancialYear=>}SALES_IN_KL_LAST_YEAR))*100
,'##.##'))
,
)