Find top 3 values across months at ONCE and present them graphically

[SouthKaDaaku]

I'm a owner of 10 theatres and I wish to track the earnings of those theatres daily. Everyday i want to know my top 3 theatres. I have two questions here :

1) Is it possible to get the top 3 data for 1/2/6 months at once from one pivot table or will I have to do find such data one by one?
2) This is an offshoot of the previous question. Once I get this data, is it possible for multiple column charts to be created automatically or will this too have to be created one by one?

Sharing a reference file for better understanding.

Thanks in advance

 

[vletm]

SouthKaDaaku
Everyday i want to know my top 3 theatres ... as below.

Do Your data really have to be as You've given?
If You're asking something per 1/2/6 months ... and You're giving as a sample 14days data from one months ... how to solve 6 months top three?

 

[SouthKaDaaku]

SouthKaDaaku
Everyday i want to know my top 3 theatres ... as below.
View attachment 77514
Do Your data really have to be as You've given?
If You're asking something per 1/2/6 months ... and You're giving as a sample 14days data from one months ... how to solve 6 months top three?

Hey vletm, the method you have suggested will surely work. But it will become very tedious if I have to sit and sort for data that is more than 5-6 days. Right?

I wanted to be able to get the top 3 values for every day at once instead of doing this function daily.

As for the 6 month part, it was just to extrapolate and put into context that such task will become tedious to the core.

Thank you, though

 

[vletm]

SouthKaDaaku
That was a sample - how to get that kind ... after You really know what do You need ... those daily results could get ... 'automatic'.
I tried to ask about Your data - You seems to skip that wondering.
Many things depends - how is Your data?
eg .. if Your data has date in one column ... then You could get Your top 3 ... the most right side ... hmm?

 

[p45cal]

In the attached, in the background, using Power Query, I have rearranged your data so it looks like this (it's called unpivoting):

which can now be repivoted as you wish.

There is a sample pivot at cell A16; the Theatre column is filtered by top 3 sum of Value. The pivot is charted. There is an added timeline at cell J14, here you can select your time frames either using the usual keyboard combination of Shift while choosing your dates; you can also select your dates by months or quarters or days. When you do so, the chart and pivot will update straightaway.



When you add more data to your table, make sure the table enlarges to include you new data:

and refresh the pivot with a right-click and Refresh.

 

[SouthKaDaaku]

Hey p45cal,

This is a dope solution )))

It also shows that I need to learn Power Query to let the data do my bidding.

What i did was, I used the LARGE function to get the top 3 values. Then I combined INDEX+MATCH along with LARGE to get the theatre name.
But since i wanted it date wise, I put a date wise drop down and wanted my LARGE function to lookup the date and return the values. It was at this point where I was stuck. I know it will be a roundabout and long drawn out solution but can you make me understand how to get top 3 values based on the date? So that based on the date change, the top 3 values and the names also will change?

Thanks in advance )

 

[SouthKaDaaku]

SouthKaDaaku
That was a sample - how to get that kind ... after You really know what do You need ... those daily results could get ... 'automatic'.
I tried to ask about Your data - You seems to skip that wondering.
Many things depends - how is Your data?
eg .. if Your data has date in one column ... then You could get Your top 3 ... the most right side ... hmm?

Ok...thank you for your time

 

[SouthKaDaaku]

In the attached, in the background, using Power Query, I have rearranged your data so it looks like this (it's called unpivoting):
View attachment 77517
which can now be repivoted as you wish.

There is a sample pivot at cell A16; the Theatre column is filtered by top 3 sum of Value. The pivot is charted. There is an added timeline at cell J14, here you can select your time frames either using the usual keyboard combination of Shift while choosing your dates; you can also select your dates by months or quarters or days. When you do so, the chart and pivot will update straightaway.

View attachment 77518

When you add more data to your table, make sure the table enlarges to include you new data:
View attachment 77519
and refresh the pivot with a right-click and Refresh.

Hey p45cal,

I did it on my own using Power Query and found out that it is so simple.

Thanks a lot again