topic How to Hide rows if two expression subtractions is zero in QlikView

How to Hide rows if two expression subtractions is zero

sangeeth8787 — Sat, 16 Nov 2024 01:35:59 GMT

Hi,

I have three expressions on my chart

Month Exp1 Exp2 Exp2-Exp1

Jan 10 10 0

Feb 25 35 10

March 45 45 0

I want to see only Feb Month Data, Is it possible to hide the other two rows based on the expressions result.

Thanks

Re: How to Hide rows if two expression subtractions is zero

Kushal_Chawda — Fri, 03 Jan 2020 11:55:56 GMT

Suppress zero value option won't work here until you have Exp1 & Exp2, because they have values for the month. It will work if your chart only have single expression Exp1-Exp2.

But what you can do is to create calculated dimension like below and allow 'suppress when value is null' option for dimension

= aggr(if(Exp1-Exp2=0,null(),Month), Month)

Re: How to Hide rows if two expression subtractions is zero

doris456 — Fri, 03 Jan 2020 12:02:58 GMT

Thanks for sharing this info

Re: How to Hide rows if two expression subtractions is zero

sangeeth8787 — Fri, 03 Jan 2020 12:06:15 GMT

Hi Kush,

I am glad for your message, for simplicity I have used Months here, but my dimensions is something like salesId, millions of rows, I guess there will be performance issues. And also on other hand, I have 4 dimensions in my pivot chart.

Thanks

Re: How to Hide rows if two expression subtractions is zero

Kushal_Chawda — Fri, 03 Jan 2020 17:51:42 GMT

Another way is to create a set expression.

Let's say you want to hide the SalesID dimension for below expressions

Expr1 - Sum(Sales)

Expr2- Sum(Qty)

Your expression will look like below

Expr1:

Sum({<SalesID ={"=sum(Sales)-sum(Qty)>0"}>}Sales)

Sum({<SalesID ={"=sum(Sales)<>sum(Qty)"}>}Sales)

Expr2:

Sum({<SalesID ={"=sum(Sales)-sum(Qty)>0"}>}Qty)

Sum({<SalesID ={"=sum(Sales)<>sum(Qty)"}>}Qty)

Expr1-Expr2

sum(Sales)-sum(Qty)

This way, Suppress zero value option should work

Re: How to Hide rows if two expression subtractions is zero

Anil_Babu_Samineni — Fri, 03 Jan 2020 12:26:59 GMT

Try this way for 1st and 2nd measures?

Expression 1 : Sum({<Month={"=Sum(Exp1)>0"}>} Exp1)

Expression 2 : Sum({<Month={"=Sum(Exp2)>0"}>} Exp2)

Re: How to Hide rows if two expression subtractions is zero

sangeeth8787 — Fri, 03 Jan 2020 14:04:31 GMT

Hi Kush,

I have tried by taking all other dimension other than salesId and used these expressions but still not able to hide rows.

Thanks

Re: How to Hide rows if two expression subtractions is zero

sangeeth8787 — Fri, 03 Jan 2020 14:05:18 GMT

Hi Anil,

I am glad for your message, but it didn't work for me.

Re: How to Hide rows if two expression subtractions is zero

Kushal_Chawda — Fri, 03 Jan 2020 14:06:35 GMT

Would you be able to share the screenshot of expressions used and what you are getting in chart?

Re: How to Hide rows if two expression subtractions is zero

sangeeth8787 — Fri, 03 Jan 2020 14:43:36 GMT

Exp1:

Sum({<SALES_INVOICE_ID={"=Sum(NET_INVOICE_AMT)-Sum(TOTALTICKETSVALUE)>0"}>} NET_INVOICE_AMT)

--=SUM(NET_INVOICE_AMT)

Exp2:

Sum({<SALES_INVOICE_ID={"=Sum(NET_INVOICE_AMT)-Sum(TOTALTICKETSVALUE)>0"}>} TOTALTICKETSVALUE)

--=sum((TOTALTICKETSVALUE))

Exp2-Exp1.

Re: How to Hide rows if two expression subtractions is zero

Kushal_Chawda — Fri, 03 Jan 2020 15:05:36 GMT

try this

Expr1

=if(sum(NET_INVOICE_AMT)-sum(TOTALTICKETSVALUE)=0,0,sum(NET_INVOICE_AMT))

Expr2

=if(sum(NET_INVOICE_AMT)-sum(TOTALTICKETSVALUE)=0,0,sum(TOTALTICKETSVALUE))

Re: How to Hide rows if two expression subtractions is zero

Kushal_Chawda — Fri, 03 Jan 2020 15:18:51 GMT

or just this

Exp1:

Sum({<SALES_INVOICE_ID={"=Sum(NET_INVOICE_AMT)-Sum(TOTALTICKETSVALUE)"}>} NET_INVOICE_AMT)

--=SUM(NET_INVOICE_AMT)

Exp2:

Sum({<SALES_INVOICE_ID={"=Sum(NET_INVOICE_AMT)-Sum(TOTALTICKETSVALUE)"}>} TOTALTICKETSVALUE)

--=sum((TOTALTICKETSVALUE))

Re: How to Hide rows if two expression subtractions is zero

sangeeth8787 — Fri, 03 Jan 2020 15:25:00 GMT

Hi kush,

The Set Expression one worked for me, after changing to

Sum({<SALES_INVOICE_ID={"=Sum(NET_INVOICE_AMT)-Sum(TOTALTICKETSVALUE)>0.1"}>} NET_INVOICE_AMT).

I am not sure why I am seeing this behaviour, but the difference between two columns output sometimes showing as 0.0000048 though I have round to two digits in SQL Query. Do you got any clues about it?

Thanks

Re: How to Hide rows if two expression subtractions is zero

Kushal_Chawda — Fri, 03 Jan 2020 15:39:16 GMT

No need to specify >0.1, I think set expression suggested above should work

Re: How to Hide rows if two expression subtractions is zero

Kushal_Chawda — Fri, 03 Jan 2020 15:51:02 GMT

Also, this set expression won't work properly as Subtraction may give positive or negative values, So >0.1 will exclude the actual value from both the expression. Try like below

Expr1-

Sum({<ID={"=sign(sum(Invoice)-Sum(Ticket))=1 or sign(sum(Invoice)-Sum(Ticket))=-1"}>}Invoice)

Expr2-

Sum({<ID={"=sign(sum(Invoice)-Sum(Ticket))=1 or sign(sum(Invoice)-Sum(Ticket))=-1"}>}Ticket)

Re: How to Hide rows if two expression subtractions is zero

sangeeth8787 — Fri, 03 Jan 2020 16:09:19 GMT

May I know what is the use of 1 or -1 here?

Re: How to Hide rows if two expression subtractions is zero

Kushal_Chawda — Fri, 03 Jan 2020 16:13:19 GMT

sign function just checks whether value is positive(1) or negative(-1). After subtract we may get result as positive or negative which we may need to tie it with original values

Re: How to Hide rows if two expression subtractions is zero

sangeeth8787 — Fri, 03 Jan 2020 16:47:48 GMT

This expression worked with small amendments

Sum({<SalesID ={"=fabs(sum(Sales)-sum(Qty))>0.1"}>}Sales)