Need Help in Coloring Expression based on Rule

[Anonymous]

I am trying to calculate the Variance % YTD of the total spend against total projected for the year, for that I am putting this expression: sum([Actual Spend])/(sum([AOP CapEx]+[AOP OpEx]))

Results are appearing fine.

Now I want to go one step ahead and color code the value based on condition, like if Variance % is greater than 100% show RED else Green, for that I am changing the above code like this:

if((Sum([Actual Spend]))/(Sum([AOP CapEx]+[AOP OpEx]))>1,red(255),green(255))

This expressions run without any error, but :

1. The Variance % is not properly calculating , the # should be 15% , but its showing 46.2345566777 which is not correct

2. The # didn't get any color

I am not sure what wrong I am doing, can someone please help me in solving this.

Thx

Ankit

[Anonymous]

Sure, let me prepare and share it with you.

[Anonymous]

Hi Omar

Here is the situation I am talking about;

Situation: I have some patients data in attached excel sheet, Starting from Enrolling into a therapy to delivery of Medicines s/he need to pass certain obstacles or Events in a particular order:

1. Referral Received
2. Insurance Investigation
3. Authorization from Insurance Org
4. Financial Assistance
5. Nurse Training

Every event we calling it as Service Request which have a Start Date and an End Date (In case that activity is completed).

Problem I am trying to solve

• I am looking for a chart that will show me Dates on the X axis, Activity on Y axis
• I need to show Bars starting from the event start date (like a Gantt chart, I have looked in Qlik Branch but didn’t understand what they talking about and how to implement). E.G.

my first Event Starts on 1-Jan-17 and Ends on 15-Jan-17

Second Event starts on 16-Jan-17 to 15-Feb17

So chart will show Bar 1 for activity one till 15-Jan-17, Bar 2 from 16-Jan-17 to 14-Feb-17 and so on

Thx

Ankit

[OmarBenSalem]

Hi Ankit,

See the attached app made by our friend danansell42‌.

this is the extension he's using:

Qlik Branch

I think it can help in your quest !

[Anonymous]

Hi Oman,

Thanks for the guidance, I have solved this today , but got in a trap, this timeline chart should mush have Start and END dates to be there, if I am showing let say 3 Events on my chart , and 2 have Start - End Date and One have start Date and No End Date (as its not sure) , the chart is not turning up. Do you know how to handle this situation.

Thx

Ankit

[danansell42]

Hi Ankit

I have already suggested a solution on your other post

https://community.qlik.com/message/1239927#1239927