Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
quantran
Contributor

Target Change Over Time

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?

15 Replies

Re: Target Change Over Time

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
quantran
Contributor

Re: Target Change Over Time

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.

Re: Target Change Over Time

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
kamielrajaram
Contributor III

Re: Target Change Over Time

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

quantran
Contributor

Re: Target Change Over Time

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

Re: Target Change Over Time

please share sample to test

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

Re: Target Change Over Time

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

quantran
Contributor

Re: Target Change Over Time

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%.

Re: Target Change Over Time

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)