topic Re: Target Change Over Time in QlikView

Target Change Over Time

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Target Change Over Time

Anil_Babu_Samineni — Mon, 14 Aug 2017 14:34:24 GMT

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

Re: Target Change Over Time

Anonymous — Mon, 14 Aug 2017 15:03:24 GMT

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

Anil_Babu_Samineni — Mon, 14 Aug 2017 15:09:42 GMT

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

Re: Target Change Over Time

kamielrajaram — Mon, 14 Aug 2017 15:17:36 GMT

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

Re: Target Change Over Time

Anonymous — Mon, 14 Aug 2017 15:37:28 GMT

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

Re: Target Change Over Time

Anil_Babu_Samineni — Mon, 14 Aug 2017 15:39:47 GMT

please share sample to test

Re: Target Change Over Time

Kushal_Chawda — Mon, 14 Aug 2017 15:43:32 GMT

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

Re: Target Change Over Time

Anonymous — Mon, 14 Aug 2017 15:51:31 GMT

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

Kushal_Chawda — Mon, 14 Aug 2017 16:14:36 GMT

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)

Re: Target Change Over Time

Anonymous — Mon, 14 Aug 2017 16:28:31 GMT

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?

Re: Target Change Over Time

Kushal_Chawda — Mon, 14 Aug 2017 16:31:14 GMT

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

Re: Target Change Over Time

Anonymous — Mon, 14 Aug 2017 16:42:37 GMT

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.

Re: Target Change Over Time

Anonymous — Mon, 14 Aug 2017 16:45:07 GMT

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

Re: Target Change Over Time

Anonymous — Mon, 14 Aug 2017 20:29:59 GMT

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

Re: Target Change Over Time

Kushal_Chawda — Tue, 15 Aug 2017 07:25:18 GMT

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)