topic Re: Formula Decipher Needed in QlikView

Formula Decipher Needed

JustinRamsey — Mon, 21 Jun 2021 19:12:17 GMT

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'))

Re: Formula Decipher Needed

lenka_kruse — Mon, 21 Jun 2021 22:15:23 GMT

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!

Re: Formula Decipher Needed

JustinRamsey — Tue, 22 Jun 2021 11:54:33 GMT

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.