Date fields QlikSense challenge

gorterzelf7 — Sat, 16 Nov 2024 21:38:45 GMT

I got a real challenge for you guys. I really hope somebody is going to help me, please be my guest and try to build it yourself.

I spended a hour to make a good example so i hope somebody can help me. I have added the source and the wanted outcome, which i hope should speak for itself

I really hope anybody is able to show me how this could be done in QlikSense, you can sent me the app or an example of the formulas.

If you have any questions, please do not hesitate to ask. I will try to add my example qvf also.

Thank you so very much for taking a look at this, this is really a challenge for me for a while now.

Kind Regards Martijn

Re: Date fields QlikSense challenge

gorterzelf7 — Mon, 07 Jan 2019 20:12:10 GMT

It won't let me add the QVF, the content of the attachment doesn't match it file type?

Well i post the script here:

Section 1 Orders:

Orders:
LOAD
Location,
"Date" AS %KEY_Datum,
Expected,
Expected1,
Real,
Real1,
Stock
FROM [lib://Source/SourceWeeks.xlsx]
(ooxml, embedded labels, table is Blad1);

Section 2 MasterCalender:

MasterCalendar:
Load
NUM(WEEKSTART(TempDate,0,-2)) AS WeekstartKalender,
RIGHT(YEAR(WEEKEND(TempDate,0,-2)+2),2)& IF(WEEK(WEEKEND(TempDate,0,-2)+2)<10,0&WEEK(WEEKEND(TempDate,0,-2)+2),WEEK(WEEKEND(TempDate,0,-2)+2)) AS JaarWeekKalender,
Autonumber(Week(weekstart(TempDate,0,-2)+2) & '-' & WeekYear(TempDate)) as WeekNUM
WHERE TempDate > Today()-50 AND TempDate < Today();

//=== Generate a temp table of dates ===
LOAD
date(mindate + IterNo()) AS TempDate
,maxdate // Used in InYearToDate() above, but not kept
WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/
LOAD
min(FieldValue('%KEY_Datum', recno()))-1 as mindate,
max(FieldValue('%KEY_Datum', recno())) as maxdate
AUTOGENERATE FieldValueCount('%KEY_Datum');

The outcome should be a pivot table like in the excel. I got it working but not with the -1, -2 years.

Regards Martijn

topic Re: Date fields QlikSense challenge in App Development

Date fields QlikSense challenge

Re: Date fields QlikSense challenge