I am working on a pivot table that will display data based on a calculation. For example, if we have locations with employees that have worked days and paid days, I want to see only the locations and employees where the employee's (count of paid days) - (count of worked days) > than X for a selected date range. I have an input box for the user to enter the value for X, but the issue I'm having is calculating the difference at run time based on the selected date range. Has anyone ever had to do something like this before? Is it possible to select data based on this calculation at the pivot table's runtime? Thank you for your help.
Thank you for your reply. However, this isn't quite what I am trying to do. Where you have [Start dt] and [End date] in your logic, I need to have an additional calculation. I am able to assign a value to a variable and call that variable in my statement. What I am not able to figure out is how to perform the calculation of (count of paid days) - (count of worked days) at run time, and at the correct dimension level, to only select records that meet the condition of where (count of paid days) - (count of worked days) > than X by Location and Employee for the selected date range. I will work on a sample .qvw to post.
I have done the same with an input box assigning a variable to its value and then doing the calculations using that variable Please let me know if that helps or post a quick sample qvw so I can show you my approach Regards
Here's an example of my expression for the table using Variable2 as the variable I told you about
Count(if([Start dt]<=Variable2 and [End date]>Variable2 and [CTC Cal] = '' and (IsNull([Cancel Date]) or [Cancel Date]>Variable2),[Contract #]))
Thank you for your reply. However, this isn't quite what I am trying to do. Where you have [Start dt] and [End date] in your logic, I need to have an additional calculation. I am able to assign a value to a variable and call that variable in my statement. What I am not able to figure out is how to perform the calculation of (count of paid days) - (count of worked days) at run time, and at the correct dimension level, to only select records that meet the condition of where (count of paid days) - (count of worked days) > than X by Location and Employee for the selected date range. I will work on a sample .qvw to post.
Hi Boris, this did the trick! I added counter fields in my tables so that the sum function would return the correct result. The response time of the pivot table isn't that great, so I'm still going to talk to my user about setting specific date ranges that can be calculated in the load script, but at least I now have this as an option. Thank you for your help!
