topic Re: Ignoring current selection in App Development

Ignoring current selection

Simon4 — Tue, 27 Dec 2022 09:57:44 GMT

Dear all,

I have a piece of code that calculates the margin:

MONEY( SUM(AGGR( SUM({$<LOADDATE = {'$(=$(VDATE_peildatum))'}, ORDERYEAR={'$(=$(Vcurrentyear))'}, cake_service = {'cake'}>} REVENUE) - IF( ISPROJECTMAINORDER = '-1' AND Order_cat_code = '100' AND SUM({$<LOADDATE = {'$(=$(VDATE_peildatum))'}, ORDERYEAR={'$(=$(Vcurrentyear))'}, cake_service = {'cake'}>} INITIALBUDGET) > 0.0, SUM({$<LOADDATE = {'$(=$(VDATE_peildatum))'}, ORDERYEAR={'$(=$(Vcurrentyear))'}, cake_service = {'cake'}>} INITIALBUDGET), IF( ISPROJECTMAINORDER = '-1' AND Order_cat_code = '100', (sum({$<LOADDATE = {'$(=$(VDATE_peildatum))'}, ORDERYEAR={'$(=$(Vcurrentyear))'}, cake_service = {'cake'}>} REVENUE*0.65)), SUM({$<LOADDATE = {'$(=$(VDATE_peildatum))'}, ORDERYEAR={'$(=$(Vcurrentyear))'}, cake_service = {'cake'}>} COSTS))) , Order_nmbr)) )

I want the code to ignore current selections so that the output stays te same no mather what. I tried using '1' and '$' or '-$' but they all don't seem to work. How do I approach this? I think it has something to do with the aggr function

Kind regards,

Simon

Re: Ignoring current selection

BrunPierre — Tue, 27 Dec 2022 13:08:03 GMT

MONEY( SUM( {1} AGGR( ...

Re: Ignoring current selection

Simon4 — Tue, 27 Dec 2022 13:18:03 GMT

Hi BrunPierre, thank you for your answer. unfortunatly this does not work. when i select for example 2019, the output shifts to zero.

Re: Ignoring current selection

vinieme12 — Tue, 03 Jan 2023 08:19:01 GMT

you will have you move your IF() block conditions into set analysis and ignore all selections in All aggregation functions

try below

MONEY(
SUM({1}AGGR({1}

SUM({1<LOADDATE = {'$(=$(VDATE_peildatum))'}, ORDERYEAR={'$(=$(Vcurrentyear))'}, cake_service = {'cake'}>} REVENUE)

IF(
SUM({1<ISPROJECTMAINORDER = {'-1'}, Order_cat_code = {'100'}, LOADDATE = {'$(=$(VDATE_peildatum))'}, ORDERYEAR={'$(=$(Vcurrentyear))'}, cake_service = {'cake'}>} INITIALBUDGET) > 0,
SUM({1<LOADDATE = {'$(=$(VDATE_peildatum))'}, ORDERYEAR={'$(=$(Vcurrentyear))'}, cake_service = {'cake'}>} INITIALBUDGET),
alt(sum({1<ISPROJECTMAINORDER = {'-1'}, Order_cat_code = {'100'},LOADDATE = {'$(=$(VDATE_peildatum))'}, ORDERYEAR={'$(=$(Vcurrentyear))'}, cake_service = {'cake'}>} REVENUE*0.65),
SUM({1<ISPROJECTMAINORDER = {'-1'}, Order_cat_code = {'100'},LOADDATE = {'$(=$(VDATE_peildatum))'}, ORDERYEAR={'$(=$(Vcurrentyear))'}, cake_service = {'cake'}>} COSTS)
)
)

, Order_nmbr))
)

Re: Ignoring current selection

Oliver_F — Tue, 03 Jan 2023 12:31:20 GMT

1 as a set modifier is definetly right if you want to ignore the selection.

your aggr() function does not have a set yet. you can try ....aggr({1}....

or you can add a {1} at the very first line (even above money()) and remove all the $ where you do definetly need them. This is a relatively new feature in Qlik Sense where you can have a "global" set at the beginning that affects all the following sets.

Re: Ignoring current selection

Oliver_F — Tue, 03 Jan 2023 12:32:31 GMT

did you checked the preview of your formula? it sounds like your date variables might be affected by the selection.