Suppressing bar with no value on bar chart

[Anonymous]

Some Background:

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

1. Sum of Sales Amount for current year( Sorting of companies in bar graph happen by this expression)
2. 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

.

[neelamsaroha157]

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))

[neelamsaroha157 · Accepted solution]

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.

[Anonymous]

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

[neelamsaroha157]

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%'))

)

[neelamsaroha157]

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%')

)

[neelamsaroha157]

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

[Anonymous]

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

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))

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

[Anonymous]

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

[Anonymous]

neelamsaroha1575‌

It seem to be some issue with my data.Nevermind

Thanks for your immense help in this problem

please just explain me the

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))

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

[neelamsaroha157]

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

Check out this for details -

Sets intersection | QlikView Help, Tips & Hints