topic Suppressing bar with no value on bar chart in QlikView

Suppressing bar with no value on bar chart

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

Some Background:

I have a graph which displays stacked bars in it based on two expressions

Sum of Sales Amount for current year( Sorting of companies in bar graph happen by this expression)
The actual bars for each company which come from expression 1 are showing the year over year % change in sales of two products A, B for each company(All the companies sell only two products A and B)

So from above two points the dimension of my bar graph is

Company
Product

The actual expression which I have are

Expression 1 (corresponding to point 1)

sum(<Category = {'TPC'}>Sales)

Expression 2(Corresponding to point 2)

(sum(<Category = {'TPC'}>Sales) - sum(<Category = {'TPC'} Year = {'$(vPreviousYear)'}>Sales)) /

sum(<Category = {'TPC'} Year = {'$(vPreviousYear)'}>Sales)

The Problem:

Now for chart the expression 1 is the hierarchically before than expression 2(In Properties -> Expressions section).

So I get all the companies (sorted on the basis of sum of sales ) for the selected year.But when the expression 2 gets evalueted(which calculates Year over Year change) there is no bar appearing for some companies.

The reason being those companies were not there in previous year due to which the denominator of Expression 2

sum(<Category = {'TPC'} Year = {'$(vPreviousYear)'}>Sales)

evaluates to 0.This produces a - (I think Null value indication by Qlikview) when I see the expression for a particular company which was not in previous year

So finally in graph for such companies I dont not get any bar but do get its name appeared.

My aim is that I dont want such companies to appear on graph( the companies for which Expression 2 would evaluate to null)

Also another case is where company may be there in selected year and previous year but one of the product would not be sold in previous year.

For example Company XYZ may have sold product A and B both in 2018 but just B in 2017.

So when Expression 2 is evaluated for product A it will give the same error as mentioned above

Some one please help me with this.I have spent an ample amount of time going over community and tried all possible solutions but still not getting over this

Re: Suppressing bar with no value on bar chart

simospa — Sun, 26 Aug 2018 15:11:42 GMT

Nikhil,

please attach a .qvw so we can help you better.

Anyway, in the meantime, please note that the correct way to use a variable to evaluate on a Section Access is with double quotes, so the correct sintax is

sum(<Category = {'TPC'} Year = {"=$(vPreviousYear)"}>Sales)

instead of

sum(<Category = {'TPC'} Year = {'$(vPreviousYear)'}>Sales)

Simone

Re: Suppressing bar with no value on bar chart

Anonymous — Sun, 26 Aug 2018 15:44:15 GMT

Thanks Simone,

Point taken about variable syntax.

The system on which I am working on which I am working for that upload is disable due to which I cannot upload the .qvw file

If you require any other clarification then please ask.I will answer each and every one

Re: Suppressing bar with no value on bar chart

Anil_Babu_Samineni — Mon, 27 Aug 2018 07:14:51 GMT

Click that after Reply and use Attach from Right corner if bottom to attach files

Re: Suppressing bar with no value on bar chart

Anonymous — Mon, 27 Aug 2018 13:20:46 GMT

Hi simone.spanio‌loveisfail Please find the qvw file attached as requested.

In the graph you can see that XYZ has "text on axis" values as 120 which is sum of sales of product A and product B for 2018 Q1

The graph shows the year of year change for product A and B in sales amount(Sales gone down by 33% for product B in 2018 Q1 as compared to that of 2017 Q1 and gone up for Product A by 60%)

Everything is fine when I have both products(A, B) for a company in 2017 Q1 and 2018 Q1 but when one of the product is missing in any of the year then I get a null value for Sales total(Null value for Text on axis)

For example:

For Company PQR I have product A in 2017 Q1 but it is not in 2018 Q1.So I get a bar saying -100% decrease in sales for 2018 Q1 which is correct.But instead of -(Qlikveiw null) on text on axis I want Sales amount of 2018 Q1 there which is 70.(I know that is not for product A but for product B).If that is not possible then I would prefer that company PQR should not appear at all on the graph.
Similary for company ABC.It has Product A and B both in 2018 Q1 but just Product B in 2017 Q1 the bar in graph says increase of 33% sales for 2018 Q1 which is correct but I do get a Null on text on axis.I either want total sales amount of 2018 Q1 as text on axis which is 120 or else ABC should not appear at all

In a nutshell what I feel is when in both year the values of dimensions (Product A and Product B) are same the result is expected but when there is mismatch(2017 Q1 has Product A and 2018 Q1 has Product B) then I get weird results.

P.S: I have changed the expression 1 from:

sum(<Category = {'TCP'}>Sales)

if(sum({<Financial_Year = {'$(Previous_Year)'}, Quarter = {'$(Selected_Quarter)'}, Category = {"TCP"}>} Sales)>0,

Aggr(sum({$ <Category = {"TCP"} >} Sales),Company))

Just making sure that a particular company exists in both years.Due to which WSA do not appears in bar chart.

Re: Suppressing bar with no value on bar chart

neelamsaroha157 — Mon, 27 Aug 2018 13:37:15 GMT

Check this out. let me know if it meets your requirement..

Re: Suppressing bar with no value on bar chart

Anonymous — Mon, 27 Aug 2018 13:56:32 GMT

Can you please provide the modifications you made.I am not able to open the file since I am using a personal edition.

Re: Suppressing bar with no value on bar chart

Anonymous — Mon, 27 Aug 2018 14:38:46 GMT

I checked it and still I am getting Null Values for some companies.

Re: Suppressing bar with no value on bar chart

Anonymous — Mon, 27 Aug 2018 14:48:05 GMT

After I select 2018 Q1 my expected output is as follows

Re: Suppressing bar with no value on bar chart

neelamsaroha157 — Mon, 27 Aug 2018 15:55:36 GMT

I was able to make the 'Values' field work with the below expression -

if(IsNull(Aggr(NODISTINCT Sum({<Company=P({<Financial_Year={'2018'}>}Company)> * <Company=P({<Financial_Year={'2017'}>}Company)>}Sales), Company)),Null(),Aggr(NODISTINCT Sum(Sales), Company))

BUT

the 'change' expression is not giving the bar value.

I am working on it but by the time I make it work, I thought I should share at least this part.

Re: Suppressing bar with no value on bar chart

neelamsaroha157 — Mon, 27 Aug 2018 15:56:48 GMT

changed the hard coded values with the variables -

if(IsNull(Aggr(NODISTINCT Sum({<Company=P({<Financial_Year={'$(Selected_Year)'}>}Company)> * <Company=P({<Financial_Year={'$(Previous_Year)'}>}Company)>}Sales), Company)),Null(),Aggr(NODISTINCT Sum(Sales), Company))

Re: Suppressing bar with no value on bar chart

neelamsaroha157 — Mon, 27 Aug 2018 17:54:09 GMT

The bar for the PQR is not appearing because of the 'division by 0' error for product B and the other row for the same company for product A is being excluded as we have a condition to do calculation when there is value for current year. I hope this makes sense.

Re: Suppressing bar with no value on bar chart

Anonymous — Tue, 28 Aug 2018 08:49:05 GMT

I am just overwhelmed by the amount of effort you have put into to help me.

Really appreciate it .

Just a last request for PQR.

Is it possible to exclude PQR altogether since the value for dimension(Product) in two years is different(Product A in 2017 and Product B in 2018).I mean this could be a robust solution.

Just consider those products which are present in both years for year over year change

Once again Thanks a lot for you immense effort into this

Re: Suppressing bar with no value on bar chart

neelamsaroha157 — Tue, 28 Aug 2018 13:45:28 GMT

I am glad I am able to help

If you just need cases where the value needs to be greater than 0 in both the years then you can use -

Value:

If(sum({<Financial_Year={'$(Selected_Year)'}>}Sales)>0 and sum({<Financial_Year={'$(Previous_Year)'}>}Sales)>0,

Aggr(NODISTINCT Sum(Sales), Company))

Change:

=If(sum({<Financial_Year={'$(Selected_Year)'}>}Sales)>0 and sum({<Financial_Year={'$(Previous_Year)'}>}Sales)>0,

if(GetFieldSelections(Quarter),

num(

(sum({<Financial_Year = {'$(Selected_Year)'}, Quarter = {'$(Selected_Quarter)'}>} Sales) -

sum({<Financial_Year = {'$(Previous_Year)'}, Quarter = {'$(Selected_Quarter)'}>} Sales))/

sum({<Financial_Year = {'$(Previous_Year)'}, Quarter = {'$(Selected_Quarter)'}>} Sales),'#,##0%'),

num(

(sum({<Financial_Year = {'$(Selected_Year)'}>} Sales) -

sum({<Financial_Year = {'$(Previous_Year)'}>} Sales))/

sum({<Financial_Year = {'$(Previous_Year)'}>} Sales),'#,##0%'))

)

Re: Suppressing bar with no value on bar chart

neelamsaroha157 — Tue, 28 Aug 2018 13:55:48 GMT

forgot to add the Quarter part -

Value :

If(sum({<Financial_Year={'$(Selected_Year)'}, Quarter = {$(='[' & If(GetPossibleCount(Quarter)=1, Concat(DISTINCT Quarter),' ') & ']')}>}Sales)>0 and sum({<Financial_Year={'$(Previous_Year)'}, Quarter = {$(='[' & If(GetPossibleCount(Quarter)=1, Concat(DISTINCT Quarter),' ') & ']')}>}Sales)>0,

Aggr(NODISTINCT Sum(Sales), Company))

Change:

=If(sum({<Financial_Year={'$(Selected_Year)'},Quarter = {$(='[' & If(GetPossibleCount(Quarter)=1, Concat(DISTINCT Quarter),' ') & ']')}>}Sales)>0 and sum({<Financial_Year={'$(Previous_Year)'}, Quarter = {$(='[' & If(GetPossibleCount(Quarter)=1, Concat(DISTINCT Quarter),' ') & ']')}>}Sales)>0,

num(

(sum({<Financial_Year = {'$(Selected_Year)'}, Quarter = {$(='[' & If(GetPossibleCount(Quarter)=1, Concat(DISTINCT Quarter),' ') & ']')}>} Sales) -

sum({<Financial_Year = {'$(Previous_Year)'}, Quarter = {$(='[' & If(GetPossibleCount(Quarter)=1, Concat(DISTINCT Quarter),' ') & ']')}>} Sales))/

sum({<Financial_Year = {'$(Previous_Year)'}, Quarter = {$(='[' & If(GetPossibleCount(Quarter)=1, Concat(DISTINCT Quarter),' ') & ']')}>} Sales),'#,##0%')

)

Re: Suppressing bar with no value on bar chart

neelamsaroha157 — Wed, 29 Aug 2018 19:49:35 GMT

Hi, Just wanted to check if it worked or not?

Re: Suppressing bar with no value on bar chart

Anonymous — Sat, 01 Sep 2018 05:16:13 GMT

Extremely sorry for late reply.It works fine.But in some cases I do see a "-" value.I was trying by myself to understand what formula you applied and was trying to modify it but no success yet.

My actual qlikview app has many filters than the file attached by me(The file just has year quarter filter).When I put on those filters a null value appears in front of those company which were not present in this year.They show a reduction of 100% in product sales but a null symbol appears for them in front of them(Which is correct logically since they were not present in this year).

Just telly me if my understanding of the below formula is correct or not

Get the companies which are present in both years

For such companies get the sum of selected year

If it is null put a null value for sales

Else calculate the sum

Correct me If I am wrong anywhere.

Also I will try to create a similar qvw and attach it for you to understand the problem better

Re: Suppressing bar with no value on bar chart

Anonymous — Sat, 01 Sep 2018 10:15:13 GMT

I am still looking into the exact problem.But till now what I found is the 'Value' expression is not having affect of any filter on it apart from 'Year' but the 'Change' expression is getting affected by filter apart from 'Year'.

I will update as soon as I get something more concrete

Re: Suppressing bar with no value on bar chart

Anonymous — Sun, 02 Sep 2018 13:32:48 GMT

neelamsaroha1575‌

It seem to be some issue with my data.Nevermind

Thanks for your immense help in this problem

please just explain me the

The part which I am not understanding is

Sum({<Company=P({<Financial_Year={'$(Selected_Year)'}>}Company)> * <Company=P({<Financial_Year={'$(Previous_Year)'}>}Company)>}Sales), Company)

The multiplication of two possible values of companies.I am not able to understand the logic in it

Re: Suppressing bar with no value on bar chart

neelamsaroha157 — Tue, 04 Sep 2018 13:00:21 GMT

I am glad it worked. '*' in set analysis is for the intersection.

Check out this for details -

Sets intersection | QlikView Help, Tips & Hints