JasTatla

New Member
Joined
Aug 18, 2014
Messages
4
hello all - what i am trying to do is take the values from a timeline slicer and use them in an IF statement.

the formula would look somnthing like this in excel =if((Date)=(Slicer_Datekey),True,False)

the slicer_datekey could contain multiple dates. any ideas on how i would approach this?

thanks
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Assuming you only need to worry about *1* value out of the slicer, you can do... pretty much any aggregation function you want on the table that feeds data to the slicer.

=IF (MAX(MyDatesTableThatFeedsSlicer[Date]) > "1/1/2010", "Newish", "Oldish")
 
Upvote 0
thanks for the reply, i should prehaps elaborate on what i am trying to achive.

i have 2 tables, Date and Active. Active contains multiple date fields that have relationships with the date table, these are Logged, Resolved, Closed, Aborted, reopened. the report i am running displays logged, Resolved and Aborted based on the date Timeline the report is sliced on.
what i need to be able to report on is for the given date period the report is sliced on the number of records that the reopened and resolved date are in that period.
example

if report was sliced on a single day 18/01/2014

Ref LoggedResolved AbortedReOpened
115/01/201418/01/2014N/A18/01/2014
220/01/201420/01/2014N/AN/A
330/02/201418/03/201419/03/2014

<colgroup><col><col span="4"></colgroup><tbody>
</tbody>

in the above example ref 1 would be counted

example 2

if report was sliced on multiple days 18/03/2014-22/03/2014

Ref LoggedResolved AbortedReOpened
115/01/201418/01/2014N/A18/01/2014
220/01/201420/01/2014N/AN/A
303/02/201518/03/201419/03/2014
403/02/201518/03/201420/03/2014
503/02/201518/03/201422/03/2014

<colgroup><col><col span="4"></colgroup><tbody>
</tbody>

ref 3,4,5 would be counted


hope this makes more sense
thanks
 
Upvote 0
I assume only one of the date relationships are "active", since...well, you can't really do it another way. Though, I suppose you could have multiple date tables linked to your multiple date fields, ... I'll ignore that :)

I'm not super sure how you want the slicer to work. I am assuming you want it like... if March 18th is sliced, you want it to show rows that were aborted on that date, or they were re-opened on that date, ... or they were resolved on that date...

Assuming so, it might be interesting to unpivot the data (such that you get a row for each date, instead of a column for each date). In general... things always go better in power pivot if you do that :)

Otherwise, I think you are looking at having all the relationships inactive, so the slicer doesn't actually do anything.

Then, have calculations that counts by each of the data types.

ResolvedCount := CALCULATE(COUNTROWS(MyTable), USERELATIONSHIP(MyTable[ResolvedDate], Date[Date]))
(AbortedCount...)
(ReopenedCount...)
AllDateCount = [ResolvedCount] + [AbortedCount] + [ReopenedCount]

And you are going to end up filtering based on that AllDateCount, by straight excel techniques. Setup a Value Filter on the pivot table to only show rows where AllDateCount > 0.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top