topic Re: Drill-down Expression in Visualization and Usability

Drill-down Expression

Thu, 18 Aug 2016 17:29:17 GMT

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

Re: Drill-down Expression

sunny_talwar — Thu, 18 Aug 2016 17:40:39 GMT

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

Re: Drill-down Expression

Thu, 18 Aug 2016 17:44:27 GMT

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.

Re: Drill-down Expression

sunny_talwar — Thu, 18 Aug 2016 17:47:37 GMT

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

Re: Drill-down Expression

Thu, 18 Aug 2016 17:53:27 GMT

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?

Re: Drill-down Expression

sunny_talwar — Thu, 18 Aug 2016 17:55:52 GMT

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

vRQ1E/vRQ1B

$(vRQ1E)/$(vRQ1B)

$(=vRQ1E)/$(=vRQ1B)

Re: Drill-down Expression

Thu, 18 Aug 2016 18:04:09 GMT

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.

Re: Drill-down Expression

Thu, 18 Aug 2016 18:06:52 GMT

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

Re: Drill-down Expression

sunny_talwar — Thu, 18 Aug 2016 18:13:16 GMT

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

Re: Drill-down Expression

Thu, 18 Aug 2016 18:19:50 GMT

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