Expression to achieve the in text box - for the latest Financial Year and Finanacial Month

[ashmitp869]

Hi All,

I want to achieve the result 16 % at the text box in my chart -

Here is the expression - 

=Num(Aggr(Count({<NextWardIdentifier={'TLR','TRANSIT','HKHTL'},[Ward Episode End Date.FinancialYear]={'$(=Max([Ward Episode End Date.FinancialYear]))'},[Ward Episode End Date.Month]={'$(=Max([Ward Episode End Date.Month]))'}>}[Total Ward Episodes])/(Count({<NextWardIdentifier={'TLR','TRANSIT','HKHTL','NA'},[Ward Episode End Date.FinancialYear]={'$(=Max([Ward Episode End Date.FinancialYear]))'},[Ward Episode End Date.Month]={'$(=Max([Ward Episode End Date.Month]))'}>}[Total Ward Episodes]) - Count({<NextWardIdentifier={'NA'},[Episode Mode of Separation Code]={'9'},[Ward Episode End Date.FinancialYear]={'$(=Max([Ward Episode End Date.FinancialYear]))'},[Ward Episode End Date.Month]={'$(=Max([Ward Episode End Date.Month]))'}>} [Total Ward Episodes])),[Ward Episode End Date.FinancialYear],[Ward Episode End Date.Month] ) ,'#,##0.#%')

Dim : =[Ward Episode End Date.Month],=[Ward Episode End Date.FinancialYear]

Thanks in advance

[MayilVahanan]

Hi

Try like Avg() or Count() or sum() before Aggr() based on ur requirement

=Num(Avg(Aggr(Count({<NextWardIdentifier={'TLR','TRANSIT','HKHTL'},[Ward Episode End Date.FinancialYear]={'$(=Max([Ward Episode End Date.FinancialYear]))'},[Ward Episode End Date.Month]={'$(=Max([Ward Episode End Date.Month]))'}>}[Total Ward Episodes])/(Count({<NextWardIdentifier={'TLR','TRANSIT','HKHTL','NA'},[Ward Episode End Date.FinancialYear]={'$(=Max([Ward Episode End Date.FinancialYear]))'},[Ward Episode End Date.Month]={'$(=Max([Ward Episode End Date.Month]))'}>}[Total Ward Episodes]) - Count({<NextWardIdentifier={'NA'},[Episode Mode of Separation Code]={'9'},[Ward Episode End Date.FinancialYear]={'$(=Max([Ward Episode End Date.FinancialYear]))'},[Ward Episode End Date.Month]={'$(=Max([Ward Episode End Date.Month]))'}>} [Total Ward Episodes])),[Ward Episode End Date.FinancialYear],[Ward Episode End Date.Month] ) ),'#,##0.#%')

[ashmitp869]

Hi Mayil,

its not working. its showing 0.0 % when trying with Count() and Sum().

Even if I selected the Year and Month.

[MayilVahanan]

Can you send the sample file to verify?

[ashmitp869]

Hi Mayil,

Here i s the sample.

[MayilVahanan · Accepted solution]

Hi

PFA

[ashmitp869]

Thank you very much.