topic Re: Creating a Light Graph with dates expressions in QlikView

Creating a Light Graph with dates expressions

Wed, 25 Nov 2020 16:16:04 GMT

Hello eveyone,

I am working on a project where i need create a semaphore graph which alternate the color depending on the results of the conditions below:

1) If the "Expected Date" minus today's date is less than 3 months, the light should be green;

2) If the "Expected Date" minus today's date is greater than 3 monhts, the light should be yellow;

3) If the "Expected Date" minus today's date is greter than today's date, the light should be red;

"Expected Date" is a date field from my database (i.e 18/03/2018);

I'm not sure if I can do subtraction operations using dates on QlikView.

How should be my expression to make this works on QlikView?

Regards,

Re: Creating a Light Graph with dates expressions

sunny_talwar — Mon, 21 Mar 2016 18:27:23 GMT

Dates are dual (meaning they are both text and number) so you can perform subtraction on dates.

If(AddMonths(Today(), -3) - [Expected Date] < 0, Green(),

If(AddMonths(Today(), -3) - [Expected Date] > 0 and Today() > [Expected Date], Yellow(), Red()))

Re: Creating a Light Graph with dates expressions

swuehl — Mon, 21 Mar 2016 18:28:07 GMT

SET DateFormat = 'DD/MM/YYYY';

Then you should be able to just subtract dates, like

=If( [Expected Date]- Today() <90, Yellow() )

Re: Creating a Light Graph with dates expressions

Mon, 21 Mar 2016 18:58:05 GMT

The graph is asking for a Minimum and Max values, what should be added into this propertie?

Thanks

Re: Creating a Light Graph with dates expressions

sunny_talwar — Mon, 21 Mar 2016 18:59:45 GMT

Which chart are you working with?

Re: Creating a Light Graph with dates expressions

swuehl — Mon, 21 Mar 2016 19:21:24 GMT

Rodrigo, I assume you are using a traffic light gauge in a chart.

If so, maybe let the expression return numbers instead of color codes

=If( [Expected Date]- Today() <90, 1, If([Expected Date] <Today() , 3,2 )

Then set min and max to e.g. 0 and 3.

Disable autowidth segments and set lower bound to e.g. 0,2 and 3 for each segment.

Re: Creating a Light Graph with dates expressions

Mon, 21 Mar 2016 19:24:07 GMT

Im working with this chart:

Re: Creating a Light Graph with dates expressions

MarcoWedel — Mon, 21 Mar 2016 23:02:11 GMT

Hi,

maybe something like this:

hope this helps

regards

Marco

Re: Creating a Light Graph with dates expressions

Thu, 07 Apr 2016 13:55:01 GMT

Hi swuehl,

Thanks for your tip, with that expression the traffic light is working well.

One more question: How can i turn the light to grey when no filters are applied (E.G when the user click in Clear All filters)?

Thanks,