Qlik Community

Ask a Question

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

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

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

Contributor III
Contributor III

1.. Yes 5. I miscounted. 

2. Yes.. it works.  THANK YOU!