Set Analysis

[Architect]

Hi, there are two parts in my question

Part1>

I am having one problem related to one field "Sellable items", that contains below-given logic.

Can you please help me understand this logic:-->

"if(match(getcurrentfield(Range_Structure),'RU','Purchasing Area (POA)','Purchasing Team','Supplier','Purchasing Group','Range Status')=0,

(num(Count({<[Range Status]=,SnapNo={1}, HAVE_ASSESMENT={1},[Item Status]={Sellable}>}DISTINCT [Item No])
/
Count({<[Range Status]=,SnapNo={1}, HAVE_ASSESMENT=,[Item Status]={Sellable}>}DISTINCT [Item No])
,'# ##0,0%')),'')"

Part2>

There are 4 columns in my chart and "Sellable items" is one of them. After adding some filter, 2 rows are selected.

In 1st row "Sellable items" = 73,8%

and in 2nd row the values are = 0,0%. when I am selecting the total value by the expression, I am having a result of 74,3%.

My goal is to neglect the 2nd  row where column "Sellable items" = 0,0% so the total become 73,8% (same as 1st row)

[Architect]

Yahh but the result is coming same always.
It seems the total is taking 0 value as input.
If some how we can set a condition that will consider only non zero value
in time of calculating the total value, will resolve the problem.

[sunny_talwar]

What do you expect the number to be? I guess I am not sure what you are looking to see here?

[Architect]

I expect the total calculation should not contain any 'Zero' value.

Like I shared in the screenshots if there are two values (74 and 0)under a column (Sellable Items).

The total should contain only non zero values. So in this case total should be 74 only.

[sunny_talwar]

Lets make sure we are on the same page. Total is a single row, right? and it needs to be sum of all the rows underneath it? So, if you agree, what difference does it make if total includes or excludes 0? 74 + 0 = 74 and 74 = 74. So, why do you have issues if 0 is included? At the end of the day Total row needs to show 74, right?

[Architect]

HI,
The total is a single row, but it is not the sum of the rows. Total is
"expression total"

[sunny_talwar]

But you want it to be the sum of rows, right?

[Architect]

HI,
I believe the issue we are facing here is due to the below expression. this expression represents the Column and the Total.
Here I am having some difficulties understanding the logic. You see the highlighted region below, can you please explain how this works.

[Brett_Bleess]

I am going to be stupid here, this seemed to potentially be somewhat related to trying to do Sum of Rows in a Pivot Table, so here is the Help link on that, not sure if it may help in this case or not, Sunny and the other guys are the better experts than am I on development stuff.  Apologies if I am way off base, but it sounded like this might be what you were trying to accomplish without using the Sum of Rows setting in the Straight Table.

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/ChartFunctions/...

Regards,
Brett

[Architect]

Hi Brett

Thank you so much for the information you have provided.

Thanks for your help.

The issue here is related to the pivot table, but I am more concerned about 'Null' handling.

[Brett_Bleess]

Here are a couple of Design Blog posts that may be of some help on the Null side of things:

https://community.qlik.com/t5/Qlik-Design-Blog/Finding-NULL/ba-p/1474279

https://community.qlik.com/t5/Qlik-Design-Blog/NULL-The-Invisible-Nothing/ba-p/1467674

Here is the main link to the Design Blog area in case you want to search further for other posts:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Sorry I do not have something better for you, hopefully these may give you something that will help you get it sorted.  If you feel something is not working as documented, then I would recommend opening a support case at that point and attaching sample app etc., so the Techs can see if we can replicate things.

Regards,
Brett