Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
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
Not applicable

Re: Drill-down Expression

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

9 Replies
sunny_talwar
Not applicable

Re: Drill-down Expression

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

Not applicable

Re: Drill-down Expression

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

Re: Drill-down Expression

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

Not applicable

Re: Drill-down Expression

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

Re: Drill-down Expression

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

vRQ1E/vRQ1B


or


$(vRQ1E)/$(vRQ1B)


or


$(=vRQ1E)/$(=vRQ1B)

Not applicable

Re: Drill-down Expression

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

Re: Drill-down Expression

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

Re: Drill-down Expression

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

Re: Drill-down Expression

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