Expression reaction to a button action? - Qlik Community

jameswills · ‎2015-04-16

Hi All,

I'm after some advice if possible.

I have a basic pivot table which includes an expression that returns numeric values ranging from zero to approx. 10,000. Is it possible to use a button which when clicked would remove zero values for that expression from the table?

Any help would be greatly appreciated.

oknotsen · ‎2015-04-17

Yes, but probably in a slightly different way than you might have hoped.

What you could do is this:

Set the expression that includes the zeros in a variable and use that variable in the pivot table.

With the button, trigger an action that change the content of the variable. The new content of the variable should be the expression that you want to have now (so one that excludes the zeros).

May you live in interesting times!

robert_mika · ‎2015-04-17

Create macros as below and add one for a button:

sub SuppressOff

set obj = ActiveDocument.GetSheetObject("CH01")

set fprop = obj.GetProperties

fprop.SuppressZero = false

obj.SetProperties fprop

end sub

sub SuppressOn

set obj = ActiveDocument.GetSheetObject("CH01")

set fprop = obj.GetProperties

fprop.SuppressZero = true

obj.SetProperties fprop

end sub

jameswills · ‎2015-04-22

Thanks for the info Robert. Can I assume that if I implement the macro it would also supress zero’s for other expressions in the chart? i.e. the chart has four expressions which all return a number, would the macro suppress all expressions or is there a way to set it t only suppress zero’s for a particular expression?

Cofion/Regards

James

Arbenigol Systemau Busnes/Business Systems Specialist

Bwrdd Iechyd Prifysgol Aneurin Bevan/Aneurin Bevan University Health Board

• 01495 765459

• james.wills@wales.nhs.uk<mailto:james.wills@wales.nhs.uk>

Room 373, C Block South, Mamhilad House, Mamhilad Park Estate NP4 0YP

• http://FinanceBusinessSystemsHomepage<http://howis.wales.nhs.uk/sitesplus/866/page/47713>

P Helpwch arbed papur - oes angen i chi printio'r e-bost yma?Help save paper - do you need to print this e-mail?

PLEASE NOTE: The information contained in this message is intended for the named recipients only. It may contain privileged and confidential information and if you are not the addressee or the person responsible for delivering this to the addressee, you may not copy, distribute or take action in reliance on it. If you have received this message in error, please notify me immediately by return e-mail.

Bwriedir yr wybodaeth yn y neges hon ar gyfer y derbyniwr/wyr a enwyd yn unig. Gall gynnwys gwybodaeth freintiedig a chyfrinachol ac os nad chi yw'r cyfeiriedig na'r sawl sy'n gyfrifol am gyflwyno'r neges i'r cyfeiriedig, ni chewch gopio na rhannu'r neges na gweithredu yn ei chylch. Os ydych wedi derbyn y neges hon drwy gamsyniad, a wnewch chi roi gwybod imi ar unwaith drwy e-bost.

It should be noted that the contents of this e-mail may be subject to public disclosure under the Freedom of Information Act 2000. Therefore, the confidentiality of this message and any reply cannot be guaranteed.

Dylid nodi y gellir datgelu cynnwys yr e-bost hwn i'r cyhoedd o dan y Ddeddf Rhyddid Gwybodaeth 2000. Felly, ni ellir gwarantu cyfrinachedd y neges nac unrhyw ymateb.

jameswills · ‎2015-04-22

Thanks for the reply Onno, great idea. Could I possibly trouble you for one more piece of assistance? The expression I have which generates the numeric values is:

-

Where PO Line Amount is

Sum

({<GLPeriod.PeriodMonthRP=, GLPeriod.PeriodYearRP=, GLBudget.BudgetName=, GLBudgetPrev.BudgetName=, POHeader.ClosedCode={'OPEN'}>}

Aggr(Sum ({<GLPeriod.PeriodMonthRP=, GLPeriod.PeriodYearRP=, GLBudget.BudgetName=, GLBudgetPrev.BudgetName=, POHeader.ClosedCode={'OPEN'}>} PODist.Amount),CostCentreName,ACCAccount,JOIN_PO_CREATEPER,POHeader.PONumber,POHeader.SupplierName,REQHeader.REQNumber,POLine.ItemDescription))

and Receipted Amount is

Sum ({<GLPeriod.PeriodMonthRP=, GLPeriod.PeriodYearRP=, GLBudget.BudgetName=, GLBudgetPrev.BudgetName=, POHeader.ClosedCode={'OPEN'}>}

Aggr(Sum ({<GLPeriod.PeriodMonthRP=, GLPeriod.PeriodYearRP=, GLBudget.BudgetName=, GLBudgetPrev.BudgetName=, POHeader.ClosedCode={'OPEN'}>} PODist.AmountDelivered),CostCentreName,ACCAccount,JOIN_PO_CREATEPER,POHeader.PONumber,POHeader.SupplierName,REQHeader.REQNumber,POLine.ItemDescription))

What would I need to change the variable to, to exclude zero’s?