Calculated Dimension: Conditional IF, AND

[#1]

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?

[jonathandienst]

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

[#3]

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.

[jonathandienst · Accepted solution]

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

[#5]

Thank you very much Jonathan!