Concat and date format issue: Garbage after expression: '00'

[eikenberg]

Hello,

i cannot concate a date for using it in a set analysis. I get the error:

I already trunced the time data with "trunc(belegdatum, 'DD')" to remove the time part but it looks it is added afterwards.

Looks like concat cannot handle "space"!?

How can i slove this?

[henrikalmen]

This is not set analysis, its a "dollar expansion". What you have written gives this result:

2018-01-02 00:00:00.000002018-0-03 00:00

... and so on. And that gives you an error. At least if it's like that just by itself.

What is it that you are trying to do? And why have you wrapped the concat() function within dollar expansion?

[eikenberg]

I just wrapped this part in the concat function to see the output.

The set analysis needs a list of values, there are no range or >= and <= possibilities afaik.
So i start there with the concat and run in this error.

I need a list of the last seven days in a diagram and try to figure out the function for it. There are already some examples here in the forum but i cannot get them working so i try to break down the problem and ended by the concat.

[henrikalmen]

It would help if you provide your full set analysis attempt, but try this then:

chr(39)&$(=concat(distinct BELGDATUM, ','&chr(39))&chr(39)

I wrote this quickly and haven't really checked my syntax, but the principle is that you want the result to look like this:

'2023-01-01 00:00.00','2023-01-02 00:00:00'

That is, values separated with commas and surrounded by apostrophes (chr39).

[eikenberg]

Sorry, it is driving me mad. I setup a test app with inline data.

Would be nice if you can help me to filter the top right with an set analyse of the last seven days.

I attached the qvf file or here is the load script

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
SET FirstWeekDay=0;
SET BrokenWeeks=0;
SET ReferenceDay=4;
SET FirstMonthOfYear=1;
SET CollationLocale='de-DE';
SET CreateSearchIndexOnReload=1;
SET MonthNames='Jan.;Feb.;März;Apr.;Mai;Juni;Juli;Aug.;Sep.;Okt.;Nov.;Dez.';
SET LongMonthNames='Januar;Februar;März;April;Mai;Juni;Juli;August;September;Oktober;November;Dezember';
SET DayNames='Mo.;Di.;Mi.;Do.;Fr.;Sa.;So.';
SET LongDayNames='Montag;Dienstag;Mittwoch;Donnerstag;Freitag;Samstag;Sonntag';
SET NumericalAbbreviation='3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y';

Test:
Load * Inline [
BELEGDATUM|NETTOTOTAL
01.03.24 00:00:00.000000|6705
29.02.24 00:00:00.000000|59674
28.02.24 00:00:00.000000|64863
27.02.24 00:00:00.000000|51336
26.02.24 00:00:00.000000|63515
25.02.24 00:00:00.000000|81679
24.02.24 00:00:00.000000|20277
23.02.24 00:00:00.000000|10322
22.02.24 00:00:00.000000|28218
21.02.24 00:00:00.000000|22224
20.02.24 00:00:00.000000|60114
19.02.24 00:00:00.000000|56157
18.02.24 00:00:00.000000|10715
17.02.24 00:00:00.000000|96428
16.02.24 00:00:00.000000|62063
15.02.24 00:00:00.000000|53068
14.02.24 00:00:00.000000|93562
13.02.24 00:00:00.000000|73766
12.02.24 00:00:00.000000|25594
11.02.24 00:00:00.000000|69582
10.02.24 00:00:00.000000|86794
09.02.24 00:00:00.000000|24207
08.02.24 00:00:00.000000|17644
07.02.24 00:00:00.000000|69565
06.02.24 00:00:00.000000|86888
05.02.24 00:00:00.000000|10950
04.02.24 00:00:00.000000|98010
03.02.24 00:00:00.000000|65219
02.02.24 00:00:00.000000|42064
01.02.24 00:00:00.000000|34642
31.01.24 00:00:00.000000|41272
30.01.24 00:00:00.000000|69652
29.01.24 00:00:00.000000|40720
28.01.24 00:00:00.000000|4284
27.01.24 00:00:00.000000|43304
26.01.24 00:00:00.000000|18365
25.01.24 00:00:00.000000|75970
24.01.24 00:00:00.000000|85809
23.01.24 00:00:00.000000|44025
22.01.24 00:00:00.000000|85630
21.01.24 00:00:00.000000|28789
20.01.24 00:00:00.000000|81495
19.01.24 00:00:00.000000|29572
18.01.24 00:00:00.000000|21440
17.01.24 00:00:00.000000|87757
16.01.24 00:00:00.000000|71463
15.01.24 00:00:00.000000|5776
14.01.24 00:00:00.000000|50456
13.01.24 00:00:00.000000|55056
12.01.24 00:00:00.000000|67866
11.01.24 00:00:00.000000|74517
10.01.24 00:00:00.000000|19806
09.01.24 00:00:00.000000|99157
08.01.24 00:00:00.000000|48828
07.01.24 00:00:00.000000|93682
06.01.24 00:00:00.000000|93494
05.01.24 00:00:00.000000|70524
04.01.24 00:00:00.000000|62874
03.01.24 00:00:00.000000|16556
02.01.24 00:00:00.000000|5580
01.01.24 00:00:00.000000|16322
31.12.23 00:00:00.000000|61449
30.12.23 00:00:00.000000|79875
29.12.23 00:00:00.000000|68838
28.12.23 00:00:00.000000|26408
27.12.23 00:00:00.000000|3419
26.12.23 00:00:00.000000|74422
25.12.23 00:00:00.000000|86586
24.12.23 00:00:00.000000|82238
23.12.23 00:00:00.000000|6089
22.12.23 00:00:00.000000|38699
21.12.23 00:00:00.000000|22352
20.12.23 00:00:00.000000|55262
19.12.23 00:00:00.000000|55066
18.12.23 00:00:00.000000|90922
17.12.23 00:00:00.000000|74552
16.12.23 00:00:00.000000|95570
15.12.23 00:00:00.000000|23320
14.12.23 00:00:00.000000|20022
13.12.23 00:00:00.000000|41945
12.12.23 00:00:00.000000|78716
11.12.23 00:00:00.000000|72528
10.12.23 00:00:00.000000|84925
09.12.23 00:00:00.000000|41223
08.12.23 00:00:00.000000|83371
07.12.23 00:00:00.000000|34955
06.12.23 00:00:00.000000|63147
05.12.23 00:00:00.000000|11245
04.12.23 00:00:00.000000|67058
03.12.23 00:00:00.000000|59760
] (delimiter is '|');

Many thanks.

[henrikalmen]

You should choose a different approach if that's what you want to do. Convert your datestring to an actual date field, and then treat the dates as dates. Much easier to calculate things like "last 7 days". It seems you have no actual timestamps in your data so it could be done like this:

Test:
load date#(left(BELEGDATUM,8), 'DD.MM.YY') as BELEGDATUM, NETTOTOTAL;
Load * Inline [
BELEGDATUM|NETTOTOTAL
01.03.24 00:00:00.000000|6705
29.02.24 00:00:00.000000|59674
28.02.24 00:00:00.000000|64863
] (delimiter is '|');

[eikenberg]

I loading direkt from oracle (lib connect to). In the database it is an real date.

So i guessed it is an real date field in qlik cause the "date picker" from the bundle is working.

How can i convert an field in already loaded data?

[henrikalmen]

In the data model view you can check if the field is already interpreted as date. If it is you don’t need the date#() wrapper in my suggestion. Just look at how I did the concatenation with apostrophes. 

[MayilVahanan · Accepted solution]

Hi

Remove the timestamp from your date field

date(left(BELEGDATUM, 8), 'DD.MM.YY') as BELEGDATUM

So that, in ur front end, you can able to see only date format without any timestamp.

Set Anaysis:

Sum({<BELEGDATUM = {">=$(=Date(Max(BELEGDATUM)-6,'DD.MM.YY'))<=$(=Date(Max(BELEGDATUM),'DD.MM.YY'))"}>}NETTOTOTAL)

it will give last 7 days net total info.

[eikenberg]

In oracle date is always with time, it is not possible to cut it off. As soon as you convert something to date it has also time.

Your Set Analysis work, thanks. 

But i read it is not possible to use >= or <= in set analysis!? That info seems to be outdated.