Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Drill-down Expression

Question -

I have a drill-down bar chart that shows (Retention %).

My Logic -

I have 2 variables:

$(vRQ1B)

$(vRQ1E)

I divide by both variables to get my (Retention %).

$(=vRQ1E)/$(vRQ1B)

See Example Below -

I have Brazil selected and on the lower right had side the % of 93 is correct. Why are all the other locations showing an average when I created a drill-down?

vRQ1E:

=Count({<[Dealer Hire Date] = {'>=01/01/2016<=03/31/2016'}>}[Student ID]) - count({<[Dealer Hire Date] = {"$(= '>=' & '01/01/2016' & '<=' & '03/31/2016')"},[Dealer Term Date] = {"$(= '>=' & '01/01/2016' & '<=' & '03/31/2016')"}>} [Student ID])

vRQ1B:

=Count({<[Dealer Hire Date] = {'>=01/01/2016<=03/31/2016'}>}[Student ID])

1 Solution

Accepted Solutions
sunny_talwar

Try this:

(Count({<[Dealer Hire Date] = {'>=01/01/2016<=12/31/2016'}>}[Student ID])

-

Count({<[Dealer Hire Date] = {"$(= '>=' & '01/01/2016' & '<=' & '12/31/2016')"},[Dealer Term Date] = {"$(= '>=' & '01/01/2016' & '<=' & '12/31/2016')"}>} [Student ID]))

/

Count({<[Dealer Hire Date] = {'>=01/01/2016<=12/31/2016'}>}[Student ID])

View solution in original post

9 Replies
sunny_talwar

Have you already clicked on the green tick mark? May be it will go away once you click on it

Not applicable
Author

What's the green tick mark? The Check Mark?

It works fine when I make the selection but I want to show a ranking amongst them all to show the top location and their retention.

sunny_talwar

Right the check mark. I am not sure I understand what you trying to do. Can you may be trying explaining again?

Not applicable
Author

I want it to look like the above ^. However, that calculation is not showing what I want. If you look back up at the first part of this thread you'll see my calculation is showing an average across the board. Each location should have their own Retention % and not show an average of all locations. They should all have their own percent's which would then average out to 89% in this example.

I think it has something to do with dividing by variables for the drill-down. But I'm not sure.

I want the End Population / Beginning Population = Retention % (which I explained my logic above).

Does think make more sense?

sunny_talwar

Got it, would you be able to try out one of these variations:

vRQ1E/vRQ1B


or


$(vRQ1E)/$(vRQ1B)


or


$(=vRQ1E)/$(=vRQ1B)

Not applicable
Author

They all are still returning the average. Below is the logic I want to represent.

Count({<[Dealer Hire Date] = {'>=01/01/2016<=12/31/2016'}>}[Student ID]) - count({<[Dealer Hire Date] = {"$(= '>=' & '01/01/2016' & '<=' & '12/31/2016')"},[Dealer Term Date] = {"$(= '>=' & '01/01/2016' & '<=' & '12/31/2016')"}>} [Student ID])  /   Count({<[Dealer Hire Date] = {'>=01/01/2016<=12/31/2016'}>}[Student ID])

Maybe you'll see something that's restricting the restriction on showing per county.

Not applicable
Author

Using this expression ^ this is what I get Count({<[Dealer Hire Date] = {'>=01/01/2016<=12/31/2016'}>}[Student ID]) - count({<[Dealer Hire Date] = {"$(= '>=' & '01/01/2016' & '<=' & '12/31/2016')"},[Dealer Term Date] = {"$(= '>=' & '01/01/2016' & '<=' & '12/31/2016')"}>} [Student ID])  /   Count({<[Dealer Hire Date] = {'>=01/01/2016<=12/31/2016'}>}[Student ID])

sunny_talwar

Try this:

(Count({<[Dealer Hire Date] = {'>=01/01/2016<=12/31/2016'}>}[Student ID])

-

Count({<[Dealer Hire Date] = {"$(= '>=' & '01/01/2016' & '<=' & '12/31/2016')"},[Dealer Term Date] = {"$(= '>=' & '01/01/2016' & '<=' & '12/31/2016')"}>} [Student ID]))

/

Count({<[Dealer Hire Date] = {'>=01/01/2016<=12/31/2016'}>}[Student ID])

Not applicable
Author

You really are very brilliant. I say it every time... but I'd be lost without your knowledge Sunny!!!

Hopefully one day I can return the favor. You're teaching me a lot!!