PixelPerfect - Set Analysis with concatenated expression

[Gerdinho22]

Hi everyone,

I got stuck with my formulars in NP. The formular should calculate the amount per seller per month and with a static entyr it works, for i.e. November:

=if(num(date#(vDatumTS)) >num(date#('01.11.'&year(vDatumTS))), sum({<Datum={'>=01.11.2019<=30.11.2019'}>}[$(vQuelle).VKWert]), 0)

As you can see, my set analysis is fixed on the year 2019. To get it dynamic, I tried to put a concatenated expression between the braces, but it wasn´t taken:

sum({<Datum={'$('>=01.11.'&year(vDatumTS)&'<=30.11.'&year(vDatumTS))'}>}[$(vQuelle).VKWert])

I tried many variations (with '$(', '$(=$(', " ",... ) and counted all the qoutation marks, but nothing worked. Maybe anyone has an idea how to get it right, without creating 12 variables in QS.

Thanks 

[Lech_Miszkiewicz · Accepted solution]

If find your expression very odd.

1. for date ranges or value ranges i would normally use double quotes

2. why are you trying to concatenate variables? can't you just use $sign expansion? like

sum({<Datum={">=$(=Date(Floor(MonthStart(Datum)),'DD.MM.YYYY'))<=$(=Date(Floor(MonthEnd(Datum)),'DD.MM.YYYY'))"}>}[$(vQuelle).VKWert])

Also, you are not telling us what is the value of vDatumTS. Have you tried to use it directly in Qlik Sense or QlikView? How those variable evaluate.

Make sure your expression firs work with Qlik Sense and then you can try it in NPrinting.

[Gerdinho22 · Accepted solution]

Hi Lech,

vDatumTS is just a fix date-entry like "12.08.2019", that is used while we are switching our ERP-systems. Later it will be replaced by "today()" 😉

I´m testing the formulars every time in QS, to avoid the long loadingtime of every PP-File

Your approach was the right one and I was able to transform it successfully:

sum({<Datum={">=$(='01.11.'&year(vDatumTS)&'<=$(='30.11.'&year(vDatumTS)))"}>}[$(vQuelle).VKWert])

Thank you very much