• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Referring to timeline values in a formula

l_jg

New Member
Hi,

I have a spreadsheet where I used slicers on the year and month (yyyy-mm) that I referred to in my formulas to show a dynamic title saying "Report from Nov-2015 to Sep-2016" etc.

If there a way to refer to the minimum and maximum values selected in a timeline in a similar fashion?

The code I used to get the minimum selection on the slicer was:

=CUBERANKEDMEMBER(DataModelRef,CUBESET(DataModelRef,Slicer_YearMonth),ROW(D1))

where "DataModelRef" is the connection, and "Slicer_YearMonth" is the slicer name where I selected the month and years to show

The code used to get the maximum selection on the slicer was:
=CUBERANKEDMEMBER(DataModelRef,CUBESET(DataModelRef,Slicer_YearMonth),CUBESETCOUNT(Slicer_YearMonth))

Thanks!

Lisa
 

Hui

Excel Ninja
Staff member
Lisa

I'd think about using a max/min function on the field in the data set
An alternate method would be to do a Maxif/MinIf function on the PT directly

are you able to post the file or a sample file?
 

l_jg

New Member
Hi,

Thanks for the quick reply Hui. I managed to solve the problem, simply by referring to the "Timeline_DateKey" in the same cubeset code as above.

I never even thought of referring to the min/max values in the pivot table, but that would work just as well and with a simpler formula too! Thankyou.

I have uploaded a copy of the simplified file (Excel 2016 - macro enabled as I have a refresh button) to show how I worked it out. I have removed all the sensitive data, so only the date fields are showing, but the formulas still work.

The timeline values are in the yellow highlighted cells in rows 1 and 2.
The title on row 13 dynamically changes depending on the values selected in the slicer and the timeline.

Thanks again
Lisa
 

Attachments

Top