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?

[Anonymous]

I tried the Aggr expression.  The line is not appearing now (see image).  Also, in your code the Month is underline in red.  Is this ok to use?

[Kushal_Chawda]

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'),YourChartDimension)

Here,Month is just the example, you need to use your actual dimension of chart

[Anonymous]

I put in "HB_DenialAdj_TransactionPostMonthLabel" field which is the same field on the x-axis (labeled as Post Month).  I get the line but it does not change from 0.006 to 0.005 (I am using this instead of 0.00006 and 0.00005) from May 2017 to June 2017.   

[Anonymous]

The attached picture is of the line not changing from 0.06% to 0.50%.

[Anonymous]

Attached is a qvw that is an example of my dashboard.  I appreciate your help.

[Kushal_Chawda]

do like this, create the flag in script itself

LOAD HB_Charge_TransactionAmount,

     HB_DenialAdj_TransactionAmount,

     (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.006', '0.005')) as Flag,

     HB_DenialAdj_TransactionPostDate,

     HB_DenialAdj_TransactionPostMonthLabel

FROM

(txt, utf8, embedded labels, delimiter is ',', msq);

Now on front end use below expression

=Sum(DISTINCT Flag)