Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have been tasked with supporting a large number of applications developed by a colleague that is no longer with the company. He was def. the senior developer for all Qlik apps so there has been a steep learning curve to decipher some of his formulas in apps.
One I am working with now thats "supposed" to equal a value from a different report but isnt is below. I cannot make sense of what the set analysis are doing exactly. Any guidance would be appreciated. Thanks in advance!
num(sum( {$< VBAP.ERDAT_NO_DELTA = {"<$(=SELECTION_CAL.DATUM_NUM)"}, VBAP.FKDAT_FUER_AUFTRAGSBESTAND = {">$(=SELECTION_CAL.DATUM_NUM)"} >} VBAP.NETWR_EFF_LOCAL), '#.##0,00 loc'))
Field Names and rough definitions below
VBAP.FKDAT_FUER_AUFTRAGSBESTAND= Order Backlog Billing Date
VBAP.ERDAT_NO_DELTA= fixed order date
VBAP.NETWR_EFF_LOCAL= Value in local currency
SELECTION_CAL.DATUM_Num is created by the following script :
LET vSeletionStart = num(makedate(2015,1,1));
LET vSelectionEnde = num(today());
LET vAnzahlTage = $(vSelectionEnde) - $(vSeletionStart) + 1;
LOAD
recno() + $(vSeletionStart) - 1 as SELECTION_CAL.DATUM_NUM
autogenerate $(vAnzahlTage);
num(sum( {$< VBAP.ERDAT_NO_DELTA = {"<$(=SELECTION_CAL.DATUM_NUM)"}, VBAP.FKDAT_FUER_AUFTRAGSBESTAND = {">$(=SELECTION_CAL.DATUM_NUM)"} >} VBAP.NETWR_EFF_LOCAL), '#.##0,00 loc'))
Hi Justin,
he is using ad-hoc variables in the statement, i.e. variables that are generated/filled depending on current selections. Here the VBAP.ERDAT_NO_DELTA is smaller than the current value of SELECTION_CAL.DATUM_NUM and the VBAP.FKDAT_FUER_AUFTRAGSBESTAND is larger than the current value of SELECTION_CAL.DATUM_NUM. So the result of the sum will change with every selection made in the date fields.
Just as an example of how ad-hoc variables in set analysis statements work, this would give you the cumulative data of the previous year up to the current/ maximum selected month:
< Year = { $(= max(Year)-1)}, MonthNo = {"<=$(=max(MonthNo))" }, Month=, Date= >
The statement has to use " and you can use all modifiers like '<, >, <=, >=' before the variable that starts with '$('.
There are some good tutorials about set analysis floating around the forums - they are worthwhile to check out, since SA is a really powerful tool when used correctly.
Good luck!
Hi Justin,
he is using ad-hoc variables in the statement, i.e. variables that are generated/filled depending on current selections. Here the VBAP.ERDAT_NO_DELTA is smaller than the current value of SELECTION_CAL.DATUM_NUM and the VBAP.FKDAT_FUER_AUFTRAGSBESTAND is larger than the current value of SELECTION_CAL.DATUM_NUM. So the result of the sum will change with every selection made in the date fields.
Just as an example of how ad-hoc variables in set analysis statements work, this would give you the cumulative data of the previous year up to the current/ maximum selected month:
< Year = { $(= max(Year)-1)}, MonthNo = {"<=$(=max(MonthNo))" }, Month=, Date= >
The statement has to use " and you can use all modifiers like '<, >, <=, >=' before the variable that starts with '$('.
There are some good tutorials about set analysis floating around the forums - they are worthwhile to check out, since SA is a really powerful tool when used correctly.
Good luck!
Thank you so much. I have not used the ad hoc variables before so they were throwing me through a loop a bit. Thank you.