topic Re: Inline table and expression help? in QlikView

Inline table and expression help?

Mon, 06 Oct 2014 00:19:00 GMT

Hello,

I am trying to accomplish the following in a chart:

1. Status = In Progress and End Quarter has not passed , GREEN

2. Status = Not Started and Start Quarter has passed , RED

3. Status = In Progress and End Quarter has passed , RED

My start and end quarters are 1,2,3,4 in the database. How do I compare to current quarter? Is there a current quarter function in Qlikview?

I created an inline table that has Year+Quarter, Quarter, Start Quarter, End Quarter columns.

The data looks like 14-1, 1, 01/01/2014, 03/31/2013.

How do I compare the start quarter that has integers to this inline table and create the above 3 conditions.

I have this working so far

=if(Status='Not Started' and START_QUARTER>1,RGB(255,153,153), IF(Status = 'In-Progress',RGB(0,255,0)))

Can anyone please suggest a way?

Thanks.

Re: Inline table and expression help?

Gysbert_Wassenaar — Mon, 06 Oct 2014 09:21:00 GMT

Perhaps you're looking for the InQuarter function. From the help file:

InQuarter (date, basedate , shift [, first_month_of_year = 1])

returns true if date lies inside the quarter containing basedate. The quarter can be offset by shift. Shift is an integer, where the value 0 indicates the quarter which contains basedate. Negative values in shift indicate preceding quarters and positive values indicate succeeding quarters. If you want to work with (fiscal) years not starting in January, indicate a value between 2 and 12 in first_month_of_year.

Examples:

inquarter ( '2006-01-25', '2006-01-01', 0 ) returns true

inquarter ( '2006-01-25', '2006-04-01', 0 ) returns false

inquarter ( '2006-01-25', '2006-01-01', -1 ) returns false

inquarter ( '2005-12-25', '2006-01-01', -1 ) returns true

inquarter ( '2006-01-25', '2006-03-01', 0, 3 ) returns false

inquarter ( '2006-03-25', '2006-03-01', 0, 3 ) returns true

Re: Inline table and expression help?

Mon, 06 Oct 2014 13:44:34 GMT

Thank you for your help. I still haven't got it working. How do I compare Quarter that is an integer to the current quarter? I need to work this out in an expression. Thanks.

Re: Inline table and expression help?

Anonymous — Mon, 06 Oct 2014 14:47:59 GMT

This will return the Quarter Number of today() i.e. the integer for current quarter

Ceil(Month(today()) /3)

You say your start and end quarters are 1,2,3,4 in the database, so you should be able to do your comparison.

Re: Inline table and expression help?

juleshartley — Mon, 06 Oct 2014 14:52:02 GMT

I would convert the integer to an arbitrary date using MakeDate(2000,[QtrInt]*3,1)... or if you want to be a bit neater then make it the end of the quarter using Date(Floor(MonthEnd(MakeDate(etc...)

Re: Inline table and expression help?

Mon, 06 Oct 2014 15:27:40 GMT

Thanks.

I have this so far. Quarter is 1, 2, 3, 4.

=if((Status='Not Started' or Status='InProgress' and Quarter> Ceil(Month(today()) /3)) or Quarter > Ceil(Month(today()) /3), , if(Status = 'InProgress', RGB(0,255,0), RGB(255,0,0))

So, for example, if the start is 1 and end is 1, it should show red.

The above expression only shows the Green. I am trying to show the red and green background in one column.

Is there something that I am doing wrong?

Thanks.

Re: Inline table and expression help?

Wed, 15 Oct 2014 00:35:21 GMT

This is what I have so far in my expression but I do not see the colors.

=If(Status='NotStarted' and startQuarter < Ceil(Month(today()) /3), RGB(255,0,0),

If(Status = 'InProgress' or Status = 'MultiYear' or Status = 'OnGoing' and endQuarter < Ceil(Month(today()) /3),RGB(255,0,0),

If(Status='InProgress' and endQuarter > Ceil(Month(today()) /3), RGB(0,255,0),RGB(255,255,255))))

Any suggestions? Could it be because the Status = InProgress is used twice in the same expression?