Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Some Background:
I have a graph which displays stacked bars in it based on two expressions
So from above two points the dimension of my bar graph is
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
.
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))
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.
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
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%'))
)
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%')
)
Hi, Just wanted to check if it worked or not?
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
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
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
I am glad it worked. '*' in set analysis is for the intersection.
Check out this for details -