Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for 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

Exclusion Calculation Total with Filter

Hello community

I have  a text box with a calculation with the intention on displaying the 'current' month's total records where there is a date on one field and not in another.  See following:

=count(DISTINCT {<
[Report Month] = {"$(=Date(max([Report Month])))"},
ActualApprovalDt = {"*"},
ID = e({<ActualStartDate = {"*"}>}),
[Record Type] = {"Y"},
Name = {"*"}
>}
ID
)

The total.. when no selections are applied is accurate ('18').  HOWEVER, when a user selects multiple filters (Actual Approval Date - 11/2 and 11/9) the total updates to an incorrect number ('34').  The number should be '6'.

Any ideas?

Thank you

 

Labels (5)
1 Solution

Accepted Solutions
Highlighted

So, when 11/9 and 11/12 is selected, why would you want to see 6 and not 5? ID E151 seems to have an ActualStartDate associated with it, isn't it?

Capture.PNG

If you agree 5 is the right answer, you can try this

=count(DISTINCT {<
	[Report Month] = {"$(=Date(max([Report Month])))"},
	ActualApprovalDt *= {"*"},
	ID *= e({<ActualStartDate *= {"*"}>}),
	[Record Type] = {"Y"},
	Name = {"*"}
	>}
	ID
	)

View solution in original post

3 Replies
Highlighted
Partner
Partner

That is because you are ignoring

ActualApprovalDt selection in your set analysis.

i changed below to get the value you want

=count(DISTINCT {<
[Report Month] = {"$(=Date(max([Report Month])))"},
//ActualApprovalDt = {"*"},
//ID = e({<ActualStartDate = {"*"}>}),
[Record Type] = {"Y"},
Name = {"*"}
>}
ID

Highlighted

So, when 11/9 and 11/12 is selected, why would you want to see 6 and not 5? ID E151 seems to have an ActualStartDate associated with it, isn't it?

Capture.PNG

If you agree 5 is the right answer, you can try this

=count(DISTINCT {<
	[Report Month] = {"$(=Date(max([Report Month])))"},
	ActualApprovalDt *= {"*"},
	ID *= e({<ActualStartDate *= {"*"}>}),
	[Record Type] = {"Y"},
	Name = {"*"}
	>}
	ID
	)

View solution in original post

Highlighted
Contributor III
Contributor III

1.. Yes 5. I miscounted. 

2. Yes.. it works.  THANK YOU!