topic Re: Alternate State Expression Help in QlikView

Alternate State Expression Help

Ankhi — Sat, 16 Nov 2024 00:00:55 GMT

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

Re: Alternate State Expression Help

sunny_talwar — Tue, 08 Sep 2020 12:45:16 GMT

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)

Re: Alternate State Expression Help

sunny_talwar — Tue, 08 Sep 2020 12:45:56 GMT

@Ankhi or this

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

Re: Alternate State Expression Help

Ankhi — Tue, 08 Sep 2020 13:11:26 GMT

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

Re: Alternate State Expression Help

sunny_talwar — Tue, 08 Sep 2020 13:26:32 GMT

How about this?

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)

Re: Alternate State Expression Help

Ankhi — Tue, 08 Sep 2020 13:34:39 GMT

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)

Re: Alternate State Expression Help

sunny_talwar — Tue, 08 Sep 2020 13:47:08 GMT

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)

Re: Alternate State Expression Help

Ankhi — Tue, 08 Sep 2020 14:07:01 GMT

Hi Sunny,

This works. Thank you so much again.

Regards

Ankhi