topic Re: Qlik Sense - Conditional Hide / Show Measures in Pivot Table in App Development

Qlik Sense - Conditional Hide / Show Measures in Pivot Table

eng19872 — Sat, 16 Nov 2024 06:10:23 GMT

Hi,

Sample file is attached, please have a look into and suggest me the solution.

if(Sum(total <Dim1> {$<[Field 1]={1}>} [xxx]) < 0, 1, 0))

I am using pivot table and I would like to Hide / Show the measure when the total value is in Negative. There are two dimensions in the Pivot Table.

When I am writing given above expression in Qlik Sense > Measures > "Show column if" then it is not working and row is not hiding.

How to fix it?

Re: Qlik Sense - Conditional Hide / Show Measures in Pivot Table

eng19872 — Wed, 03 Apr 2019 14:17:31 GMT

Qlik Gurus, your help required.

Re: Qlik Sense - Conditional Hide / Show Measures in Pivot Table

johngouws — Thu, 04 Apr 2019 00:46:36 GMT

I have not tried this, but the only way I think you can achieve this is to make the 2 dimensions also 'calculated dimension' like it is called in QlikView. Basically you say "if(<your expression> > 0, <Dimension>).

Other than that I don't think it will work.

Have you tried not using a pivot table?

Re: Qlik Sense - Conditional Hide / Show Measures in Pivot Table

eng19872 — Thu, 04 Apr 2019 06:29:28 GMT

The expression is not working for hide / show for conditional hide show however, it works fine when I am writing in expression section.

Re: Qlik Sense - Conditional Hide / Show Measures in Pivot Table

miskinmaz — Thu, 04 Apr 2019 07:33:38 GMT

Please share the sample data.

Re: Qlik Sense - Conditional Hide / Show Measures in Pivot Table

eng19872 — Mon, 08 Apr 2019 06:08:53 GMT

Hi,

Please check attached file as shared with main thread. File name is "PositiveNegativeApp.qvf".

Re: Qlik Sense - Conditional Hide / Show Measures in Pivot Table

eng19872 — Mon, 08 Apr 2019 09:38:48 GMT

Hi Miskinmaz,

Any solution?

Re: Qlik Sense - Conditional Hide / Show Measures in Pivot Table

miskinmaz — Mon, 08 Apr 2019 10:06:16 GMT

with respect to which dimension the hide show should work? Name or product

Just to make the req clear from below screen shot please let me know which column you want to hide:

Re: Qlik Sense - Conditional Hide / Show Measures in Pivot Table

eng19872 — Mon, 08 Apr 2019 14:50:41 GMT

Hi Miskinmaz,

I need a flag when I select then there must be only Negative Values on the basis of row total.

Means, when I select flag NA - A should be appear because it's total is in Negative and when I deselect then all the values visible.

Re: Qlik Sense - Conditional Hide / Show Measures in Pivot Table

eng19872 — Tue, 09 Apr 2019 07:20:52 GMT

Hi Miskinmaz and Qlik Experts,

Can you share me the solution?

Re: Qlik Sense - Conditional Hide / Show Measures in Pivot Table

miskinmaz — Tue, 09 Apr 2019 12:02:36 GMT

You can use below expression as measure:

if(GetSelectedCount(myFilter)>0 and sum(total<Name,Product>{<Name={"=sum(aggr(sum(Sales),Name))<0"}>}Sales)<0,
sum({<Name={"=sum(aggr(sum(Sales),Name))<0"}>}Sales),

if(GetSelectedCount(myFilter)=0 ,sum(Sales)
))

Re: Qlik Sense - Conditional Hide / Show Measures in Pivot Table

eng19872 — Tue, 09 Apr 2019 12:53:13 GMT

Same result as I shared the expression with this thread.

I need expression to hide and show the row expression "Show Column if"

Re: Qlik Sense - Conditional Hide / Show Measures in Pivot Table

MynhardtBurger — Tue, 09 Apr 2019 15:20:41 GMT

I make use of the Show column if setting of a dimension.

In my case I defined a variable vShowFieldPlantCostCentre with an initial value as '0' (hidden).

Then we use the Variable Input bundled extension, where the user selects from a dropdown box whether to show the column or not. The variable input dropdown box toggles the $(vShowFieldPlantCostCentre) variable between '0' and '1'.

The Show column if expression is:

=$(vShowFieldPlantCostCentre) = 1

So if the vShowFieldPlantCostCentre variable is equal to '1' then the column will show in the pivot table.

Note that the variables are reset to their default values when the model reloads. So if the model reloads the user will see that his selections (which are linked to variables) revert back to their default values, and in this case causes this column selections to be reset.

Re: Qlik Sense - Conditional Hide / Show Measures in Pivot Table

eng19872 — Wed, 10 Apr 2019 12:13:18 GMT

Hi Mynhardtb,

I hope, you read the question. However, here is quick overview of problem.

For example: I have Pivot table with SUM Expression. When it execute expression will evaluate and generate the rows for dimension. Now, I have 10 rows 5 consist on Negative (-) and 5 Positive (+) value. I would like to hide and show the Negative (-) rows when select filter "Negative Value".

Expression and Application is already attached with this thread.

Re: Qlik Sense - Conditional Hide / Show Measures in Pivot Table

MynhardtBurger — Wed, 10 Apr 2019 18:34:49 GMT

Hi Eng19872,

See the attached file for 2 possible solutions.

In both cases you will use set analysis to filter for rows where [Sales] is negative like so:

Sum({<[Sales]={"<0"}>} [Sales])

I suspect the first solution where you use PICK to select between a SUM which adds up all [Sales] values, and one which only adds up negative [Sales} values is what you are actually after.

I used PICK because it has some performance benefits over using IF statements.

Re: Qlik Sense - Conditional Hide / Show Measures in Pivot Table

rusanov_eduard — Sat, 01 May 2021 09:01:21 GMT

Hello)
Thanks a lot for the solution. Changed it a little, instead of expanding, I used the function of the standard button "Assign value to variable")