Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
MONEY( SUM( {1} AGGR( ...
Hi BrunPierre, thank you for your answer. unfortunatly this does not work. when i select for example 2019, the output shifts to zero.
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))
)
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.
did you checked the preview of your formula? it sounds like your date variables might be affected by the selection.