Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
Partner
Partner

Date fields QlikSense challenge

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

1 Reply
Partner
Partner

Re: Date fields QlikSense challenge

 

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