50 Replies Latest reply: Feb 9, 2017 8:45 AM by Sunny Talwar

# Making my Y-axis in percentage.

How do I make my Y-axis in a graph show in percentage, I have seen other post about this, but none is working for me. e.g I have 50 items and 40 meets my selected condition, The Line/Bar Graph should show 80% i.e out of 100%????

• ###### Re: Making my Y-axis in percentage.

May be like this:

Sum(Measure)/Sum({1} Meaure)

• ###### Re: Making my Y-axis in percentage.

Hi Sunny, the result is this not as expected.

• ###### Re: Making my Y-axis in percentage.

Would you be able to share a sample? Cause images don't really help...

• ###### Re: Making my Y-axis in percentage.

The measure TICKET_ID is over 70000 rows and they come in daily, I am trying to sort them out on a monthly, using the RESOLVE_TIME(month) as you see on the X-axis. Only I want is the TICKET_ID showing in percentage not number. what do u mean sample ?? like the below

TICKET_ID   MONTH       CONDITION- SLA MET     SLA Not MET

70                 JAN                                     50               20

30                 FEB                                     25                5

60                 MAR                                    60                0

20                 APRIL                                  18                2

From that condition above, my Y-Axis should be by MONTH(X-Axis) the following;

60%     -JAN

80%     -FEB

100%   - MAR

89%     -APRIL.

will this help??

• ###### Re: Making my Y-axis in percentage.

How do you calculate CONDITION- SLA MET and SLA Not MET? Is this an expression or field?

• ###### Re: Making my Y-axis in percentage.

Yes. There is an express. Below is the expression:

IF([TIER]='GOLD',

IF([DECODE_PRIORITY]='High' AND [Arrival to Resolve Days]<=1,'SLA MET',

IF([DECODE_PRIORITY]='Urgent' AND [Arrival to Resolve Days]<=1,'SLA MET',

if([DECODE_PRIORITY]='Medium' AND [Arrival to Resolve Days]<=3,'SLA MET',

IF([DECODE_PRIORITY]='Low' AND [Arrival to Resolve Days]<=10,'SLA MET','SLA Not Met')))),

IF([DECODE_PRIORITY]='High' AND [Arrival to Resolve Days]<=3,'SLA MET',

IF([DECODE_PRIORITY]='Urgent' AND [Arrival to Resolve Days]<=3,'SLA MET',

IF([DECODE_PRIORITY]='Medium' AND [Arrival to Resolve Days]<=10,'SLA MET',

IF([DECODE_PRIORITY]='Low' AND [Arrival to Resolve Days]<=20,'SLA MET','SLA Not Met')))))

Which works fine, with the two expected output.

• ###### Re: Making my Y-axis in percentage.

I am not sure I am following completely... is there any way you can share your .qvf?

• ###### Re: Making my Y-axis in percentage.

The CONDITION - I created using a MASTER with the multiple IF condition in the function expression.

• ###### Re: Making my Y-axis in percentage.

Before Qlik Sense, I used excel, I want to display something like the Above(from excel). Thanks for your attention and quick response Sunny.

• ###### Re: Making my Y-axis in percentage.

And I cant find the option of saving as .QVf file???

• ###### Re: Making my Y-axis in percentage.

Hi Sunny T

As Requested, it has been attached. Thanks

• ###### Re: Making my Y-axis in percentage.

Try this?

Count({<TIER = {'GOLD'}, DECODE_PRIORITY = {'High'}, [Arrival to Resolve Days] = {'<=1'}>+

<TIER = {'GOLD'}, DECODE_PRIORITY = {'Urgent'}, [Arrival to Resolve Days] = {'<=1'}>+

<TIER = {'GOLD'}, DECODE_PRIORITY = {'Medium'}, [Arrival to Resolve Days] = {'<=3'}>+

<TIER = {'GOLD'}, DECODE_PRIORITY = {'Low'}, [Arrival to Resolve Days] = {'<=10'}>+

<TIER -= {'GOLD'}, DECODE_PRIORITY = {'High'}, [Arrival to Resolve Days] = {'<=3'}>+

<TIER -= {'GOLD'}, DECODE_PRIORITY = {'Urgent'}, [Arrival to Resolve Days] = {'<=3'}>+

<TIER -= {'GOLD'}, DECODE_PRIORITY = {'Medium'}, [Arrival to Resolve Days] = {'<=10'}>+

<TIER -= {'GOLD'}, DECODE_PRIORITY = {'Low'}, [Arrival to Resolve Days] = {'<=20'}>}[TICKET_ID_])/Count(TICKET_ID_)

One thing I would suggest is if you can create the SLA flag in the script.... If you do that, your life would be so much easier.

• ###### Re: Making my Y-axis in percentage.

Hi Sunny T,

Thanks for your reply.We are close, but that expression includes both SLA MET and SLA Not MET, The Line is only meant to reflect one of them when the condition is selected. When I clicked SLA MET, it all went to 100% which is not true.

• ###### Re: Making my Y-axis in percentage.

It include SLA Not Met? I though that SLA Met + SLA Not Met  = 100%? Is that not true? For instance, apr-16 have 97.2% met and 2.8% not met? Is that not right?

• ###### Re: Making my Y-axis in percentage.

Trying clicking the SLA met Condition, everything becomes 100%. I want a situation like when SLA Not Met is picked, the line should show 2.8% for APR 16 and when SLA MET is chosen 97.2% SHOWS. is it possible ??

• ###### Re: Making my Y-axis in percentage.

Can you do this transformation in the script?

IF([TIER]='GOLD',

IF([DECODE_PRIORITY]='High' AND [Arrival to Resolve Days]<=1,'SLA MET',

IF([DECODE_PRIORITY]='Urgent' AND [Arrival to Resolve Days]<=1,'SLA MET',

if([DECODE_PRIORITY]='Medium' AND [Arrival to Resolve Days]<=3,'SLA MET',

IF([DECODE_PRIORITY]='Low' AND [Arrival to Resolve Days]<=10,'SLA MET','SLA Not Met')))),

IF([DECODE_PRIORITY]='High' AND [Arrival to Resolve Days]<=3,'SLA MET',

IF([DECODE_PRIORITY]='Urgent' AND [Arrival to Resolve Days]<=3,'SLA MET',

IF([DECODE_PRIORITY]='Medium' AND [Arrival to Resolve Days]<=10,'SLA MET',

IF([DECODE_PRIORITY]='Low' AND [Arrival to Resolve Days]<=20,'SLA MET','SLA Not Met'))))) as SLA

and share an updated sample?

• ###### Re: Making my Y-axis in percentage.

Hi Sunny T,

When u say script you meant the DATA LOAD EDITOR right, where I have my string connections ???

• ###### Re: Making my Y-axis in percentage.

Yes, within a LOAD statement in the script or inside your SQL

• ###### Re: Making my Y-axis in percentage.

The IF statement looks right, but its coming up with an error at the line with the first closing brackets, for some weird reason.

• ###### Re: Making my Y-axis in percentage.

Would you be able to share the screenshot for the error you are seeing?

• ###### Re: Making my Y-axis in percentage.

I usually test every of my script on my SQL developer and as u can see, the highlighted line is where its saying there is an error.

• ###### Re: Making my Y-axis in percentage.

I don't know SQL that well, but I have not seen if statements in SQL before... May be you need to use Case or Decode?

• ###### Re: Making my Y-axis in percentage.

Why wouldn't it work in the master item I created?? I know SQL don't like IF statement, that's why I avoid it at all cost. its just a conditional statement ??

• ###### Re: Making my Y-axis in percentage.

"Why wouldn't it work in the master item I created??"

What is a master item?

• ###### Re: Making my Y-axis in percentage.

lol. The Master Item or variable as you may call it.

• ###### Re: Making my Y-axis in percentage.

Or better still, I know there is a symbol that can be in the expression, I can use to permanently show the SLA MET line graph and not change when another selection is chosen, but changes as the items are added to the monthly Dimension????

• ###### Re: Making my Y-axis in percentage.

I am not sure I am able to follow you...

• ###### Re: Making my Y-axis in percentage.

It's a pity we cant resolve this, but thanks for your time. And if you come up with an idea, please let me know.

Thanks

• ###### Re: Making my Y-axis in percentage.

The most efficient way to do what you are trying to do is to create a field in the scrip which says SLA Met vs SLA Not Met. I am not sure what issues you are running into doing this? But I will take a look at your script and may be able to suggest something within Qlik Sense. Once we have this field... I think you will be all set to go.

• ###### Re: Making my Y-axis in percentage.

Hi Sunny T

Thanks a lot for your, I will try all possible means here myself.

• ###### Re: Making my Y-axis in percentage.

Can you try to run the attached.

If there are any errors, please post screenshots. Thanks

• ###### Re: Making my Y-axis in percentage.

Yes It is loading perfectly. But how will it affect the line chart to do what I want??

• ###### Re: Making my Y-axis in percentage.

Can you share the updated qvf and I can show you how it can be used

• ###### Re: Making my Y-axis in percentage.

I think the attachment didn't come through... can you attach again?

• ###### Re: Making my Y-axis in percentage.

DONE!!

• ###### Re: Making my Y-axis in percentage.

Based on your selection in the new SLA filter you can see the below chart change from SLA Met (%) to SLA Not Met (%). What do you want to see by default when neither of them are selected?

• ###### Re: Making my Y-axis in percentage.

A 100% and if it is possible. can the chart only react when the SLA MET/SLA NOT MET is clicked and nothing ?

• ###### Re: Making my Y-axis in percentage.

So no other selection will ever impact this chart except for selection in SLA field? Try this

Count({1<SLA = \$::SLA>}[TICKET_ID_])/Count({1}TICKET_ID_)

• ###### Re: Making my Y-axis in percentage.

Hi Sunny T,

It is solved, what if I want other selection to affect, what do I take off, just in case???

• ###### Re: Making my Y-axis in percentage.

Just use this

Count([TICKET_ID_])/Count({<SLA>}TICKET_ID_)

Also, if I may ask, would you consider marking helpful response (in addition to the correct response) so that people landing on this huge thread can look for responses which might help them troubleshoot a similar issue. You can mark as many response as you want as helpful, but I would not overdo to make sure that only responses which are really helpful to other users are marked.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

• ###### Re: Making my Y-axis in percentage.

Will do it now. Thanks Thanks thanks!! cant be happy enough

• ###### Re: Making my Y-axis in percentage.

Good Long stalwar1 Bhai. Happy to learn few things from this Queue post

• ###### Re: Making my Y-axis in percentage.

Hello Sunny T,

thanks for the help, it was a huge success in my team, due to its success other teams has asked me to do a more bigger APP foe the whole of the company. going from the below code, which u altered and I ran in my script;

IF([TIER]='GOLD',

IF([DECODE_PRIORITY]='High' AND [Arrival to Resolve Days]<=1,'SLA MET',

IF([DECODE_PRIORITY]='Urgent' AND [Arrival to Resolve Days]<=1,'SLA MET',

If([DECODE_PRIORITY]='Medium' AND [Arrival to Resolve Days]<=3,'SLA MET',

IF([DECODE_PRIORITY]='Low' AND [Arrival to Resolve Days]<=10,'SLA MET','SLA Not Met')))),

IF([DECODE_PRIORITY]='High' AND [Arrival to Resolve Days]<=3,'SLA MET',

IF([DECODE_PRIORITY]='Urgent' AND [Arrival to Resolve Days]<=3,'SLA MET',

IF([DECODE_PRIORITY]='Medium' AND [Arrival to Resolve Days]<=10,'SLA MET',

IF([DECODE_PRIORITY]='Low' AND [Arrival to Resolve Days]<=20,'SLA MET','SLA Not Met'))))) as SLA;

I wanna add another TIER to the IF statement, instead of having just GOLD and SILVER  Tier, I wanna to add BRONZE to it, with its conditions too. I did the following;

IF([TIER]='GOLD',

IF([DECODE_PRIORITY]='High' AND [Arrival to Resolve Days]<=1,'SLA MET',

IF([DECODE_PRIORITY]='Urgent' AND [Arrival to Resolve Days]<=1,'SLA MET',

If([DECODE_PRIORITY]='Medium' AND [Arrival to Resolve Days]<=3,'SLA MET',

IF([DECODE_PRIORITY]='Low' AND [Arrival to Resolve Days]<=10,'SLA MET','SLA Not Met')))),

IF([TIER]='SILVER',

IF([DECODE_PRIORITY]='High' AND [Arrival to Resolve Days]<=3,'SLA MET',

IF([DECODE_PRIORITY]='Urgent' AND [Arrival to Resolve Days]<=3,'SLA MET',

IF([DECODE_PRIORITY]='Medium' AND [Arrival to Resolve Days]<=10,'SLA MET',

IF([DECODE_PRIORITY]='Low' AND [Arrival to Resolve Days]<=20,'SLA MET','SLA Not Met'))))),

IF([DECODE_PRIORITY]='High' AND [Arrival to Resolve Days]<=40,'SLA MET',

IF([DECODE_PRIORITY]='Urgent' AND [Arrival to Resolve Days]<=40,'SLA MET',

IF([DECODE_PRIORITY]='Medium' AND [Arrival to Resolve Days]<=40,'SLA MET',

IF([DECODE_PRIORITY]='Low' AND [Arrival to Resolve Days]<=40,'SLA MET','SLA Not Met')))))) as SLA;

but this doesn't seem to work. Can you help me out please?

• ###### Re: Making my Y-axis in percentage.

Try this, I think you had an extra parenthesis:

IF([TIER]='GOLD',

IF([DECODE_PRIORITY]='High' AND [Arrival to Resolve Days]<=1,'SLA MET',

IF([DECODE_PRIORITY]='Urgent' AND [Arrival to Resolve Days]<=1,'SLA MET',

If([DECODE_PRIORITY]='Medium' AND [Arrival to Resolve Days]<=3,'SLA MET',

IF([DECODE_PRIORITY]='Low' AND [Arrival to Resolve Days]<=10,'SLA MET','SLA Not Met')))),

IF([TIER]='SILVER',

IF([DECODE_PRIORITY]='High' AND [Arrival to Resolve Days]<=3,'SLA MET',

IF([DECODE_PRIORITY]='Urgent' AND [Arrival to Resolve Days]<=3,'SLA MET',

IF([DECODE_PRIORITY]='Medium' AND [Arrival to Resolve Days]<=10,'SLA MET',

IF([DECODE_PRIORITY]='Low' AND [Arrival to Resolve Days]<=20,'SLA MET','SLA Not Met')))),

IF([DECODE_PRIORITY]='High' AND [Arrival to Resolve Days]<=40,'SLA MET',

IF([DECODE_PRIORITY]='Urgent' AND [Arrival to Resolve Days]<=40,'SLA MET',

IF([DECODE_PRIORITY]='Medium' AND [Arrival to Resolve Days]<=40,'SLA MET',

IF([DECODE_PRIORITY]='Low' AND [Arrival to Resolve Days]<=40,'SLA MET','SLA Not Met')))))) as SLA;

• ###### Re: Making my Y-axis in percentage.

how silly I'm I. Thanks, its working.

• ###### Re: Making my Y-axis in percentage.

Awesome

• ###### Re: Making my Y-axis in percentage.

Thank you very much. I really appreciate!!!