Dimension not working

[#1]

Hi,

I have the following columns from multiple tables:

Risk - with values of High1, High, Medium and Low

Completed_Date - Date values

Application Name

I am trying to create a Bar chart that considers fulfills the given requirement which is also given below.

If the Application's risk is either High1 or High, I need to add 700 days to the Completed Date and check if that date value falls under 30, 60, 90 and 90+ days limit from today and create bars that correspond to each limit (with number of apps within those 30-60-90 and 90+ days). Similarly, if the risk of Applications is either Medium or Low, then I need to add 1000 days to the Completed Date and check if that value falls under 30, 60, 90 and 90+ days

The dimension I am using right now seems correct to me but it doesn't give me the needed four bars for 30, 60, 90 and 90+ days with correct values.

Dimension being used:

=if(RISK='High' or 'Critical' and
Date(COMPLETED_DATE)+730 <= Today()-30,'30 Days',
if(Date(COMPLETED_DATE)+700 <=Today()-60,'60 Days',
if(Date(COMPLETED_DATE)+700 <=Today()-90,'90 Days',
if(Date(COMPLETED_DATE)+700 >91,'90+ Days',

if(RISK='High' or 'Critical' and
Date(COMPLETED_DATE)+1000 <= Today()-30,'30 Days',
if(Date(COMPLETED_DATE)+1000 <=Today()-60,'60 Days',
if(Date(COMPLETED_DATE)+1000 <=Today()-90,'90 Days',
if(Date(COMPLETED_DATE)+1000 >91,'90+ Days'))))

))))

In the Expressions, I am counting the number of Application Names.

Is this the only way to get m requirement or am I missing something?

[#2]

you say it does not give the 4 bars you are expecting - what is it giving you?

have you thought of putting this type of logic in the load script isntead of the front end so your buckets will already be updated and calculted isntead of determining each time the object is referenced?

[#3]

It just gives me 30 Days bar and another bar with incorrect Stacked values. I can't do the load in the script as I am not the one who is loading the data. I am only using the data that has been already loaded and should do it from Object perspective.

[swuehl]

I think there are several issues with your expression.

- You are using same RISK values for both comparisons

- I think you should group the if() statements differently, but to give a better advise, what do you want to achieve in more detail? Are you looking to group your COMPLETED_DATE+X values into date ranges TODAY() - Y or TODAY() + Y?

- Date(COMPLETED_DATE)+700 >91,'90+ Days'

Doesn't seem to make much sense. the comparison will always be true.

Please post some sample lines of data and your expected outcome.

[#5]

I would suggest discussing putting this in the load since it is much more effecient

short of that, is the 30 day bar correct?

I would suggest crating a table with the calculated dimesnions showing the dates calcualted from

Date(COMPLETED_DATE)+700

Today()-60

as well as all of the other dates - this will create a table showing the dates of each side of the calcualton - from there we can hopefully determine where it is going wrong

[#6]

also in your second set of expressions, you need to change the risk values you are checking against to be medium, low

[#7]

My Expected Outcome:

Under 30, 60, 90 and 90+ days, the 'Number of Apps' bars should show up.

The conditions for the apps to be categorized into these days are as follows:

If the Application's risk is High1 or High , then add 700 days to its Completed_Date and check if that date falls under 30 days from Today. Similarly, for 60 days, 90 days and 90+days.

If the Application's risk is Medium or Low, then add 1000 days to its Completed_Date and check if that date falls under 30-60-90 and 90+ days from Today's Date.

So there should be 4 bars under 30 Days, 60 Days, 90 Days and 90+ Days. Or else, 2 bars under 30-60-90 and 90+ is also fine.

I am using the given Dimension to create the bars and counting the Apps in Expression using Count(Distinct(Application_Name))

[swuehl]

Still not 100% sure if you mean 30 days ahead of time or past 30 days, when you are saying '30 days from Today'.

I assume you mean 30 days starting from today (though your expression tells something different).

=if(RISK='High' or RISK = 'Critical',
          If(Date(COMPLETED_DATE)+700 <= Today()+30,'30 Days',
               if(Date(COMPLETED_DATE)+700 <=Today()+60,'60 Days',
                    if(Date(COMPLETED_DATE)+700 <=Today()+90,'90 Days',
                         if(Date(COMPLETED_DATE)+700 > Today()+90 ,'90+ Days')))),

           if(RISK='Medium' or RISK = 'Low',

               If(Date(COMPLETED_DATE)+1000 <= Today()+30,'30 Days',
                        if(Date(COMPLETED_DATE)+1000 <=Today()+60,'60 Days',
                              if(Date(COMPLETED_DATE)+1000 <=Today()+90,'90 Days',
                                        if(Date(COMPLETED_DATE)+1000 >Today()+90,'90+ Days'))))

          )

)

[#9]

it does not sound like there is any way to look at the chart and see which of the 30 day is critical, high, low or medium?

Will that type of break out be necessary?  If I understand your expactions, you will have 4 bars broken down by the days but no way of knowing which are medium or critical?

[#10]

The Risk Column has these High1, High, Medium and Low values for all the Applications.