Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I'm trying to create a bar chart that sums Amounts for stock that is current and unexpired. I try to achieve this using pre-set dates based on 2 criteria:
sum if i) Initiation_Date <= Date
AND ii) Expiry_Date > Date
Where "Initiation_Date" and "Expiry_Date" are fields in the data, and "Date" is a user-entered value or range of discrete values. Below is the expression I have entered in the Edit Expression field for Used Dimensions, but it seems to return an error.
=if([INITIATION_DATE] <= 31/12/2013 AND [EXPIRY_DATE] > 31/12/2013, sum([Amounts]))
User defined date: 31/12/2013
Also, if I were to substitute a variable for the Date field, how would I pass this into the expression based on a user-defined date or range of dates?
Then you would probably find it easier if you use dates in a field, such as the end year date as you suggest. Then the dimension could be the compare date (for example CompareDate) and the expression could be:
=Sum(if([INITIATION_DATE] <= [CompareDate] AND [EXPIRY_DATE] > [CompareDate], [Amounts]))
CompareDate should be in an island table (no association to the rest of the model. The users could make selections in CompareDate to see specific values and you could make the calculation conditional on the users having selected between one and a maximum number of compare dates (eg 12; to avoid killing your server by trying to calculate for all dates at once)
Calculation condition:
=GetSelectedCount(CompareDate) >= 1 And GetSelectedCount(CompareDate) <= 12
HTH
Jonathan
Hi
For a single value, you can use:
=Sum(if([INITIATION_DATE] <= '$(vSingleDate)' AND [EXPIRY_DATE] > '$(vSingleDate)', [Amounts]))
Let vSingleDate = '31/12/2013';
I am not clear what you would be comparing when using multiple date values. Do you want to compare the min and max values in some way, or do you want to do the single expression calculation, but applied to each date in turn, summing the total?
HTH
Jonathan
Thank you Jonathan,
For multiple dates, I would be looking at performing the above calculation for a range of dates:
vDateValues = '31/12/2013', '31/12/2012', '31/12/2011', '31/12/2010'
so I would have a chart to show me valid total stock values as at the end of the years in the range 2010-2013.
I'm just not sure how best to incorporate the string of dates into the expression and how best to interface with the user. Perhaps this could be achieved though a multiple selection box with pre-defined end of year dates.
Then you would probably find it easier if you use dates in a field, such as the end year date as you suggest. Then the dimension could be the compare date (for example CompareDate) and the expression could be:
=Sum(if([INITIATION_DATE] <= [CompareDate] AND [EXPIRY_DATE] > [CompareDate], [Amounts]))
CompareDate should be in an island table (no association to the rest of the model. The users could make selections in CompareDate to see specific values and you could make the calculation conditional on the users having selected between one and a maximum number of compare dates (eg 12; to avoid killing your server by trying to calculate for all dates at once)
Calculation condition:
=GetSelectedCount(CompareDate) >= 1 And GetSelectedCount(CompareDate) <= 12
HTH
Jonathan
Thank you very much Jonathan!