Alternate State Expression Help

[Ankhi]

Hi All,

Can you please help me in correcting the below expression. The Expenditure Type is in a different state in my report . State A. How to rewrite the below with Expenditure Type in State A.

=Sum({<
[Cost Status]={'ACT'},
[Expenditure Type]={'*'}-{'$(=GetFieldSelections([Expenditure Type]))'}
>}Amount)

Please help.

Regards

Ankhi

[sunny_talwar]

May be this

So, you want to exclude which ever Expenditure Type is selected in the alternate state? May be try this

=Sum({<[Cost Status] = {'ACT'}, [Expenditure Type] = e({<[Expenditure Type] = [State A]::[Expenditure Type]>})>} Amount)

[sunny_talwar]

@Ankhi or this

=Sum({<[Cost Status] = {'ACT'}, [Expenditure Type] = e({<[Cost Status] = {'ACT'}, [Expenditure Type] = [State A]::[Expenditure Type]>})>} Amount)

[Ankhi]

Hi Sunny,

Thanks for the reply. There is only one value in the list Box [Expenditure Type]. The expression that I have used to create the list box is below . So it only shows 'Non-Specific Accruals'

=If([Expenditure Type]='Non-Specific Accruals',[Expenditure Type]).

Now in the measure I want to show sum of all Expenditure Types except 'Non-Specific Accruals' when I select 'Non Specific Accruals' in Expenditure Type

So I used..

[Expenditure Type]={'*'}-{'$(=GetFieldSelections([Expenditure Type]))'} and it was giving me correct output.

The only thing now changed is I have to put the Expenditure Type in a diff State.  I tried your expression.

But only shows output when I select the value from Expenditure Type otherwise shows 0.

Full expression below:

=Sum({<
[Cost Status]={'ACT'},
Master.MonthYr =,
[Archive Status]=,
Master.Date = {"<=$(=max({StateA}Master.Date))"},
//[Expenditure Type]={'*'}-{'$(=GetFieldSelections([Expenditure Type]))'}
[Expenditure Type]= e({<[Expenditure Type] = [Alt]::[Expenditure Type]>})
>}Amount)

Hope I have been able to make my req clear.

Thanks again.

Regards

Ankhi

[sunny_talwar]

How about this?

=Sum({<
[Cost Status]={'ACT'},
Master.MonthYr =,
[Archive Status]=,
Master.Date = {"<=$(=max({StateA}Master.Date))"},
[Expenditure Type] -= p({<[Expenditure Type] = [Alt]::[Expenditure Type]>})
>}Amount)

or you can try this

=Sum({<
[Cost Status]={'ACT'},
Master.MonthYr =,
[Archive Status]=,
Master.Date = {"<=$(=max({StateA}Master.Date))"},
[Expenditure Type]={'*'}-{'$(=GetFieldSelections([Expenditure Type], ',', 100, 'Alt'))'}
>}Amount)

[Ankhi]

Hi Sunny,

Thanks again. The first expression again gives the same as with e. That is only on selection of Expenditure_Category, it shows data else 0. 2nd expression says 'Error in Expression'. Expression used is below

=Sum({<
[Cost Status]={'ACT'},
Master.MonthYr =,
[Archive Status]=,
Master.Date = {"<=$(=max({StateA}Master.Date))"},
[Expenditure Type]-=p({<[Expenditure Type] = [Alt]::[Expenditure Type]>})
//[Expenditure Type]={'*'}-{'$(=GetFieldSelections([Expenditure Type], ',', 100, 'Alt'))'}
>}Amount)

[sunny_talwar · Accepted solution]

Sorry, my bad, try this for second expression

=Sum({<
[Cost Status]={'ACT'},
Master.MonthYr =,
[Archive Status]=,
Master.Date = {"<=$(=max({StateA}Master.Date))"},
[Expenditure Type]={'*'}-{"$(=GetFieldSelections([Expenditure Type], ',', 100, 'Alt'))"}
>}Amount)

[Ankhi]

Hi Sunny,

This works. Thank you so much again.

Regards

Ankhi