-
1. Re: Date parameter / calculated field help
Simon RuncSep 27, 2016 12:43 PM (in response to Mikey Michaels)
1 of 1 people found this helpfulhi Mikey,
So it's hard to be exact without seeing and example of your data (ideally a packaged workbook)...but would this work? (I'm assuming the parameter is the numbers 1-4, representing the selected quarter)
First I'm going to alter the parameter as follows
Notice that the numbers (the value the parameter returns) is the Period, but the user sees the Alias (Quater 1, Quarter 2...etc.)
I'm then also going to create an Integer version of your Fiscal Period
[Fiscal Period - INT]
INT( [Fiscal Period])
Then, create 2 new fields which return the required result for each, by coding the filtering in the calculation...
[Seat UM - Selected Quarter]
IIF([Fiscal Period - INT] = [Select Quarter], [Seat UM], NULL)
and bring this in as a COUNT
[Revenue - Selected Quarter]
IIF([Fiscal Period - INT] <= [Select Quarter] AND [Fiscal Period - INT] > [Select Quarter] -3, [Revenue],NULL)
and bring this in as a SUM
Then as the user selects the Quarter, one will bring back just a COUNT of Seat UM for the last period of the quarter, and SUM([Revenue]) for the whole quarter.
btw I may not have all the correct brackets...etc. (I rely on the editor to spot such things for me!)
Hope that helps, but let me know if not.
-
2. Re: Date parameter / calculated field help
Benjamin Greene Sep 27, 2016 12:48 PM (in response to Mikey Michaels)2 of 2 people found this helpfulSince you only want to have one Fiscal Quarter field, here is what I would do:
1. Right-click Fiscal Period and select Change Data Type>Number (Whole)
2. Edit Fiscal Quarter so it is
IF [Fiscal Period]<=3 THEN "Q1"
ELSEIF [Fiscal Period]<=6 THEN "Q2"ELSEIF [Fiscal Period]<=9 THEN "Q3"
ELSE "Q4"
END3. Create a Quarter-End Seats UM calculation that is
COUNT(IF [Fiscal Quarter]="Q1" AND [Fiscal Period]=3 THEN [Seats UM]
ELSEIF [Fiscal Quarter]="Q2" AND [Fiscal Period]=6 THEN [Seats UM]
ELSEIF [Fiscal Quarter]="Q3" AND [Fiscal Period]=9 THEN [Seats UM]
ELSEIF [Fiscal Quarter]="Q4" AND [Fiscal Period]=12 THEN [Seats UM]
END)
Now, the Quarter-End Seats UM calculation will only have values for the last month of every quarter, and you will be able to sum revenue across every month within a given quarter.
-
3. Re: Date parameter / calculated field help
Mikey Michaels Sep 27, 2016 3:17 PM (in response to Simon Runc)Ugh - sorry Simon
I cannot believe I did not attach my sample workbook (now attached).
Also, I believe I let out a key detail to my question - again sorry for this.
On the attached, I have a parameter called "Select Date Level" -> this allows a user to look at the data by month and also by quarter. From looking at your solution, it looks like the quarter-end issue for Seats UM has been solved - thank you!, but how can I adjust your solution so when my user select "Month" they will see the sum of Revenue and Seats UM for all periods (201601 -> 201612)? Below is what I expect the result to be when selecting Month and Quarter:
Correct result when selecting Month from "Select Date Level"
Correct result when selecting Quarter from "Select Date Level"
-
Date parameter help.twbx 12.6 KB
-
-
4. Re: Date parameter / calculated field help
Benjamin Greene Sep 28, 2016 7:35 AM (in response to Mikey Michaels)2 of 2 people found this helpfulMy original solution still works for what you are trying to achieve, just with one minor change. Make the formula in step 3 this instead:
IF [Parameters].[Select Date Level]="Month"
THEN SUM([Seats UM])
ELSE SUM(IF [Fiscal Quarter - Calculated Field]="Q1" AND [Fiscal Period]=3 THEN [Seats UM]
ELSEIF [Fiscal Quarter - Calculated Field]="Q2" AND [Fiscal Period]=6 THEN [Seats UM]
ELSEIF [Fiscal Quarter - Calculated Field]="Q3" AND [Fiscal Period]=9 THEN [Seats UM]
ELSEIF [Fiscal Quarter - Calculated Field]="Q4" AND [Fiscal Period]=12 THEN [Seats UM]
END)
END
And then also remove that Select Date Level filter and you will get the numbers you are expecting.