Target Change Over Time

[Anonymous]

I have a target line that changes over time.  If the Post Date is <=5/31/2017 then value should be '0.00006'.  If the Post Date is >=  6/1/2017 then the value should be '0.00005'.

I have created a line bar within a chart.  The following codes were put into the line object in the Expression>Definition .  None of them works. They all return '0.00005':

If (HB_DenialAdj_TransactionPostDate<= date('05-31-2017', 'MM-DD-YYYY') and HB_DenialAdj_TransactionPostDate>= date('01-31-2013', 'MM-DD-YYYY'), '0.00006', '0.00005')

If (HB_DenialAdj_TransactionPostDate<= '05-31-2017' and HB_DenialAdj_TransactionPostDate>= '01-31-2013', '0.00006', '0.00005')

If (HB_DenialAdj_TransactionPostDate<= '05-31-2017', '0.00006', '0.00005')

If (HB_DenialAdj_TransactionPostDate<= '05/31/2017', '0.00006', '0.00005')

Can I do this using a table in the database (that has the dates and target values) or inline?

[Anil_Babu_Samineni]

How about this?

If (HB_DenialAdj_TransactionPostDate<= date('05-31-2017', 'MM-DD-YYYY'), '0.00006',

If (HB_DenialAdj_TransactionPostDate>= date('01-31-2013', 'MM-DD-YYYY'),  '0.00005'))

[Anonymous]

Thank you.  I tried it.  However, the line is not appearing in the chart.  How do I get the line to appear.  Please see attached.

[Anil_Babu_Samineni]

May be suppress when value is null from presentation tab? or please share sample work file

[kamielrajaram]

Hi,

Have you tries flagging the rows in the script that meet your criteria and use that one field in your expressions.

Kind Regards

Kamiel

[Anonymous]

At the presentation tab, I unchecked the Suppress Zero Values but it does not change anything.

[Anil_Babu_Samineni]

please share sample to test

[Kushal_Chawda]

Use Date# to compare the value with actual date

If (floor(HB_DenialAdj_TransactionPostDate)<= floor(date#('05-31-2017', 'MM-DD-YYYY')) and floor(HB_DenialAdj_TransactionPostDate)>= floor(date#('01-31-2013', 'MM-DD-YYYY')), '0.00006', '0.00005')

[Anonymous]

The line is now appearing but the shift from 0.6% to 0.5% is not occurring.  It is just a straight line at 0.5%.

[Kushal_Chawda]

may be you need aggr function

aggr(If (floor(HB_DenialAdj_TransactionPostDate)<= floor(date#('05-31-2017', 'MM-DD-YYYY')) and floor(HB_DenialAdj_TransactionPostDate)>= floor(date#('01-31-2013', 'MM-DD-YYYY')), '0.00006', '0.00005'),Month)