need formula for tricky output

[Anonymous]

Dear All,

Need some help Please find the attachment with expected output.

Thanks

[kamal_sanguri]

here is the updated one..

I used the expression suggested by settu_periasamy‌..

[Anonymous]

Hi Settu, settu_periasamy‌

Your solution is right but when I am using this into main application then it is not showing correct values.

Please see below

Dim used :

1)Division

2)Product

3)State

4)Customer

Expression:

(Sum({$<FinancialYear=,[Posting Date]={">=$(=date(max(YearStart)))<=$(=date(max(YearEnd)))"}>} [Gross Sales Amount])

-

Sum({$<FinancialYear=,[Posting Date]={">=$(=date(max(YearStart)))<=$(=date(max(YearEnd)))"}>} [Sales Return Amount]))/100000

Contr: ?

[settu_periasamy]

Maybe try this..

if(Dimensionality()<=3,

(Sum({$<FinancialYear=,[Posting Date]={">=$(=date(max(YearStart)))<=$(=date(max(YearEnd)))"}>} [Gross Sales Amount]-[Sales Return Amount])/

Sum(TOTAL {$<FinancialYear=,[Posting Date]={">=$(=date(max(YearStart)))<=$(=date(max(YearEnd)))"}>} [Gross Sales Amount]-[Sales Return Amount])),

(Sum({$<FinancialYear=,[Posting Date]={">=$(=date(max(YearStart)))<=$(=date(max(YearEnd)))"}>} [Gross Sales Amount]-[Sales Return Amount])/

Sum(TOTAL <State> {$<FinancialYear=,[Posting Date]={">=$(=date(max(YearStart)))<=$(=date(max(YearEnd)))"}>} [Gross Sales Amount]-[Sales Return Amount])))/100000

[Anonymous]

Hi settu_periasamy‌,

I tried but not get exp output..

[settu_periasamy]

Have you tried to separate the expression and see the result is correct or not?

[Anonymous]

Yes

[settu_periasamy]

Hi,

Could you prepare the sample with your issue illustrate?

I believe you are using that expression in pivot table.

May be try this..

if(Dimensionality()<=3,

(Sum({$<FinancialYear=,[Posting Date]={">=$(=date(max(YearStart)))<=$(=date(max(YearEnd)))"}>} [Gross Sales Amount])

- Sum({$<FinancialYear=,[Posting Date]={">=$(=date(max(YearStart)))<=$(=date(max(YearEnd)))"}>}[Sales Return Amount]))/

(Sum(TOTAL {$<FinancialYear=,[Posting Date]={">=$(=date(max(YearStart)))<=$(=date(max(YearEnd)))"}>} [Gross Sales Amount])

-Sum(TOTAL {$<FinancialYear=,[Posting Date]={">=$(=date(max(YearStart)))<=$(=date(max(YearEnd)))"}>} [Sales Return Amount]),

(Sum({$<FinancialYear=,[Posting Date]={">=$(=date(max(YearStart)))<=$(=date(max(YearEnd)))"}>} [Gross Sales Amount])

- Sum({$<FinancialYear=,[Posting Date]={">=$(=date(max(YearStart)))<=$(=date(max(YearEnd)))"}>}[Sales Return Amount]))/

(Sum(TOTAL <State>{$<FinancialYear=,[Posting Date]={">=$(=date(max(YearStart)))<=$(=date(max(YearEnd)))"}>} [Gross Sales Amount])

-Sum(TOTAL <State>{$<FinancialYear=,[Posting Date]={">=$(=date(max(YearStart)))<=$(=date(max(YearEnd)))"}>} [Sales Return Amount])))