Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Alternate State Expression Help

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

Labels (1)
1 Solution

Accepted Solutions
Highlighted

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)

View solution in original post

7 Replies
Highlighted

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)
Highlighted

@Ankhi or this

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

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

 

Highlighted

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)
Highlighted
Contributor III
Contributor III

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)

Highlighted

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)

View solution in original post

Highlighted
Contributor III
Contributor III

Hi Sunny,

This works. Thank you so much again.

Regards

Ankhi