I have been able to do this with the use of Parameter controls. I will use the Superstore Sample data as my example.
First you will need to create 4 parameters:
Beginning and End of each range segment:
Once you have all four assigned to their date ranges, you can select "show parameter control" by right clicking on the parameter. This populates them on the right hand of the screen:
Now we need to tie the parameters into our calculations. I have created 2 calculation, one for each date range:
You can change the greater than or less than signs as you see fit.
I also created a % difference calculation (may be different from the one you will use):
And finally we can drop these calculations into our view.
Let me know if this isn't what you are looking for.
Two Date Ranges.twbx 1.2 MB
Thanks Josh. I have some questions:
1. You are using a list parameter for your ranges, thus, the dates won't refresh everyday. Is is possible to attain the same functionality with a 'All' parameter instead of a list one?
2. I am getting the following when I followed through the steps you provided:
I had to add 'Date' to the columns for the else it provides only nulls.
Is there a way, I could aggregate the values for the defined periods rather than the individual values by days?
1. Yes, using an 'All' versus a 'List' would work as well, the list is what worked for me.
2. Can you provide a view of your calculations? I am noticing that your % Diff calculation is still providing nulls even with the date added to the columns. I am thinking that it has something to do with how you are aggregating your Value 1 and Value 2 calculations.
Thanks for your promptness. I have managed to get the nulls out from % Diff:
Here are my Value 1, Value 2, % Diff Calculations:
Value 1: if attr([Date])>= [Date Range A Begin] and attr([Date])<= [Date Range A End] then [Score] END
Value 2: if attr([Date])>= [Date Range B Begin] and attr([Date])<= [Date Range B End] then [Score] END
% Diff: (ABS([Value 1]-[Value 2])/[Value 1])*100
The difference between your calculation and mine is that I have had to use attr() for my date as seen above otherwise it would give me a 'Cannot mix non-aggregate and aggregate functions' error.
I checked the date data type and its the same as yours.
So I am left with two issues:
1. Attr() for the dates
2. Values not updating with a change in parameter dates, Value 1 and Value 2 is populating only for the common dates
3. Need to place the day(date) pills in columns and hence am getting the day grain for the values
Looking forward to hearing from you. Thanks again!
Hmm, I see where the issue may come from:
When I replicate your aggregation, I also get nulls. So the next question I have is, what is your score calculation? We may need to change where its aggregation occurs to remove the Attribute calculation, because it doesn't work without the Date level of detail in the view.
I have two firleds: Numerator and Denominator:
and Score is a ratio of the two fields, namely, (SUM([Measure Numerator])/SUM([Measure Denominator]))*100
Is the aggregate field causing the issue?
Try updating your Value 1 and Value 2 calculations to:
SUM( IF [Date] >= [Date Range A Begin] AND [Date] <= [Date Range A End] THEN [Measure Numerator] END )
SUM( IF [Date] >= [Date Range A Begin] AND [Date] <= [Date Range A End] THEN [Measure Denominator] END ) * 100
SUM( IF [Date] >= [Date Range B Begin] AND [Date] <= [Date Range B End] THEN [Measure Numerator] END )
SUM( IF [Date] >= [Date Range B Begin] AND [Date] <= [Date Range B End] THEN [Measure Denominator] END ) * 100
Try these calculations without the [Date] pills in your columns.
Let me know if it doesn't work.