topic PixelPerfect - Set Analysis with concatenated expression in Qlik NPrinting

PixelPerfect - Set Analysis with concatenated expression

Gerdinho22 — Wed, 12 Feb 2020 14:35:50 GMT

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

Re: PixelPerfect - Set Analysis with concatenated expression

Lech_Miszkiewicz — Thu, 13 Feb 2020 05:03:34 GMT

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.

Re: PixelPerfect - Set Analysis with concatenated expression

Gerdinho22 — Thu, 13 Feb 2020 10:49:36 GMT

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