topic Re: Set Analysis in QlikView

Set Analysis

Architect — Sat, 16 Nov 2024 18:52:44 GMT

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)

Re: Set Analysis

Architect — Sat, 04 Apr 2020 14:57:23 GMT

HI @Mauritz_SA , can you please look into the issue.

Re: Set Analysis

Mauritz_SA — Mon, 06 Apr 2020 06:28:23 GMT

Hi @Architect

This is quite a complex question. I have only worked in Qlik Sense and not QlikView and getcurrentfield is not a valid function in Qlik Sense.

In Part 1 it seems as if you only want to do the calculation when the Range_Structure is not one of the following: 'RU','Purchasing Area (POA)','Purchasing Team','Supplier','Purchasing Group','Range Status'.

The rest of the calculation basically wants the number of Items which have a HAVE_ASSESMENT value of 1 out of all of the items while disregarding the [Range Status] selection and only looking at items which have a 'Sellable' [Item Status] and a SnapNo of 1. I think the Sellable needs to be in single or double quotes.

As for Part 2, I am not sure, but maybe someone like @sunny_talwar can assist.

Regards,

Mauritz

Re: Set Analysis

tresB — Mon, 06 Apr 2020 06:48:12 GMT

Hi Gaurab,

To extend what @Mauritz_SA has said,

Part1> You are probably having a group (cyclic/drill down) in chart called 'Range_Structure'. And then as stated by Mauritz, this calculation is expected to be in action when none of those fields are in chart being active.

Part2>

....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%....

Could not really follow this. Could you explain with screen shot or a sample app attached?

Re: Set Analysis

Architect — Mon, 06 Apr 2020 08:07:30 GMT

HI @tresB ,

I have attached one word Doc here to explain the issue.

Please have a look.

Re: Set Analysis

tresB — Mon, 06 Apr 2020 08:25:21 GMT

Hi,

You have created the document nicely to include details possible. Are you overlapping objects (textbox/straight table)? It seems you are using the very same expression for your 'total' row. However, I am not sure it is really context/dimension sensitive or not. You have shared lot of actual information, can you also share the sample app (remove all charts,data and then share)?

Re: Set Analysis

tresB — Mon, 06 Apr 2020 09:58:13 GMT

You can reduce application size and share here. Check: https://community.qlik.com/t5/QlikView-Documents/Preparing-examples-for-Upload-Reduction-and-Data-Scrambling/ta-p/1494262/page/2

Re: Set Analysis

sunny_talwar — Mon, 06 Apr 2020 13:20:41 GMT

Where exactly are you using an expression for Total? All I am seeing in each of the screenshot is a reference for Sellable Iteams.... Where exactly are you adding Total expression?

Re: Set Analysis

Architect — Mon, 06 Apr 2020 17:18:44 GMT

Total is nothing but a chart used here.

and in Dimension, there is no parameter added.

Re: Set Analysis

sunny_talwar — Mon, 06 Apr 2020 18:24:15 GMT

This looks like a straight table, can't you use Sum of rows to show the total?

Re: Set Analysis

Architect — Mon, 06 Apr 2020 19:13:43 GMT

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.

Re: Set Analysis

sunny_talwar — Tue, 07 Apr 2020 10:54:46 GMT

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

Re: Set Analysis

Architect — Tue, 07 Apr 2020 13:06:36 GMT

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.

Re: Set Analysis

sunny_talwar — Tue, 07 Apr 2020 13:09:33 GMT

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?

Re: Set Analysis

Architect — Thu, 09 Apr 2020 05:56:09 GMT

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

Re: Set Analysis

sunny_talwar — Thu, 09 Apr 2020 11:31:56 GMT

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

Re: Set Analysis

Architect — Thu, 09 Apr 2020 12:32:54 GMT

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.

Re: Set Analysis

Brett_Bleess — Tue, 21 Apr 2020 20:28:34 GMT

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/NestedAggregations/sum-of-rows-pivot-tables.htm

Regards,
Brett

Re: Set Analysis

Architect — Fri, 24 Apr 2020 09:54:02 GMT

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.

Re: Set Analysis

Brett_Bleess — Tue, 28 Apr 2020 12:03:29 GMT

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