I have a calculated field in my script which has the code something like below :
Select (case
when table1.HOUR_SEG_PRIME='AM-OS:12 AM - 7 AM' then 0.292
when table1.HOUR_SEG_PRIME='AM-Eng:7 AM - 9 AM' then 0.083
when table1.HOUR_SEG_PRIME='Prime Time:9 AM - 7 PM' then 0.417
when table1.HOUR_SEG_PRIME='PM-Eng:7 PM - 10 PM' then 0.125
when table1.HOUR_SEG_PRIME='PM-OS:10 PM - 12 AM' then 0.083
end)*
24 as Available_Hours
from table1
Now , the Available Hours should be calculated based on the inputs given in a calender box.
I have two fields in the table2 Inception_date and Decommision_date
when a user select a 2 dates from 2 calender boxes which gives a date range for eg, From: 01-12-2011 - To: 01-20-2011
From:
I need to calculate the available hours From : 01-12-2011 , the condition here is if 01-12-2011 is > than the dates Inception_date then 01-12-2011, else higher inception_date has to be considered for calculating available hours
To:
I need to calculate the available hours To : 01-20-2011 , the condition here is if 01-20-2011 is > than the Decommision_date then Decommion_date else 01-20-2011 has to be considered for calculating available hours.
I am unable to understand where to include these condition in the load script or in the expressions or dimensions...
Since your user is going to be making the selections on a calendar box, I would think you want to handle this logic in your application rather than the load script. If you do it outside of the script, then the user can modify the values without reloading.
First, come up with an expression based on your selections:
That should only sum when Inception_date is greater than the first entered date and Decommision_date is greater than the second entered date. You may need to do some formatting on those values to match up with the date format on your fields.
Looking at the second half of your logic, if > 1-12-2011, then Inception, else 1-12-2011, I think I need some more info on your data set up. Is there one record per dimension or do you have values for separate dates? Can you give an example of what one record or one set of records would look like?
Thank you for reply , i have attached my application by loading some sample data ,
I have'nt created variables anytimes, could you please let me know what cal1 and cal2 are ...or else suggest me something to have it in the script ? anything which is easy to understand...
The requirement is basically calculating the available hours between inception_date and decommision date , my dimension would be week and here ,
so when a user selects a range of dates if the starting date is greater than inception date then take the selected date as startdate for calculating the available hours else date the inception_date as the start date.
second logic :
if the the ending date from the selected range of the dates is greater than decommision_date then select the end date as decomission_date else take the selected end date as end date for calculating the available hours ..
vCal1 and vCal2 are variables to store your begin and end selected dates. In your example, you used 1-12-2011 and 1-20-2011. There are multiple ways to handle this variable pair.
You could use your Date field and select a range. vCal1 could be the minimum and vCal2 could be the max. If you're going to do this, you should have a field containing all possible dates.
You could also provide input boxes and let the user type in the dates manually.
I think you may run into a problem using Set Analysis, because Set Analysis does not reference dimensions. In your case, each Tester has a separate Inception date, which you will need in your comparisons.
I've attached your example with some modifications that use selections on date. I don't know if it is exactly what you want, but it should get you moving in the right direction.
Couple of question , i have copied the variable same as in your application , by my app shows some different value , for vCal1 and vCAL2,
and why did you have 1 in the chart what does that give.
And also is there a way where i can have this available hours calculation in the script ?I might be building many other graphs using this available hours , so i guess it would be easy to load it in the script..
The values of vCal1 and vCal2 are based on selections on the Date field. Change your selections and the values of those variables will change.
The expression of 1 in a chart is a good way to show all records. If you're working on an expression and it returns nulls or zeros, those records will be suppressed. Adding a 1 expression will prevent the records from being suppressed. I should have deleted it before posting as it isn't necessary.
I don't think you can put available hours in the script. Your available hours calculation is based off user selection. If you put it in the script, the users no longer have the choice. If the calculation would not depend on user selected values, then it could go in the script, but that's a decision for you based on your data and business needs.
One thing you may want to try is to create a working available hours calculation and then create a variable to hold your expression. Then you can use a dollar sign expansion on that variable in place of the expression.
Somehow , even if i change i dont get the values you get ....please help ..attached my application.I get the date values i selected if i remove $ , and the calculation does not work if i have $ , if i remove i get values , but those as expected...
