Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
glennmaldonado23
Contributor II
Contributor II

Date interval

Hi! 

I am new to programming/qlik and find it quite complicated...

Task is to create a dashboard which contains information about: 

- Number of Matricule numbers that are in the Month - Year in Guarantee.

- Number of Matricule that just entered into guarante in that Month Year. 

- Number of Matricule numbers that are leaving the guarantee in the upcoming month. 

The goal is to create a Column Bar graphic but honestly I do know how... being stucked for two weeks. 

Timelapse is Dic-2012, Jan-2013,Feb-2013..... Dic-2032 

 

** Every matricule number has a Beginn and End of Guarantee date. 

 

Labels (1)
6 Replies
cristianj23a
Partner - Creator III
Partner - Creator III

Creating a dashboard with the given requirements in QlikView/Qlik Sense involves several steps. Since you mentioned you're new to programming and Qlik, I'll try to provide a simplified step-by-step guide. Keep in mind that the exact steps might vary depending on the version of Qlik you're using, but the general approach should remain similar.

  1. Data Preparation: Make sure you have your data in a suitable format, likely a table with columns for Matricule Number, Begin Date, and End Date. Ensure the dates are in a format Qlik can understand (usually YYYY-MM-DD).

  2. Data Loading: Open your Qlik application and load the data using the script editor. This involves writing Qlik's script language (QlikView or Qlik Sense script) to load and structure your data.

  3. Date Dimensions: Create a master calendar with all relevant date values between December 2012 and December 2032. This will help you establish the time periods for your analysis.

  4. Set Analysis Expressions: You'll need to write expressions that calculate the counts based on the defined time periods. Qlik uses Set Analysis to filter data for specific dimensions.

  5. Create Visualizations: For the column bar graphic, you will create a bar chart visualization.

Here's a simplified example of how you might achieve this in Qlik Sense:

Assumptions:

  • You have a data table named "GuaranteeData" with columns: MatriculeNumber, BeginDate, EndDate.
  • You have created a master calendar with a field named "MonthYear" representing Month and Year (e.g., Jan-2013).
  • You have set up the necessary associations between your data and the master calendar.

Expressions:

1. Number of Matricule numbers in the Month-Year in Guarantee:

Count({<BeginDate={"<=$(=Date(EndOfMonth(Max(MonthYear))))"}, EndDate={">=$(=Date(StartOfMonth(Min(MonthYear))))"}>} DISTINCT MatriculeNumber)

2. Number of Matricule that just entered into guarantee in that Month-Year:

Count({<BeginDate={">$(=Date(StartOfMonth(Min(MonthYear))))"}, EndDate={">=$(=Date(StartOfMonth(Max(MonthYear))))"}>} DISTINCT MatriculeNumber)

3. Number of Matricule numbers that are leaving the guarantee in the upcoming month-year:

Count({<BeginDate={"<=$(=Date(EndOfMonth(Max(MonthYear))))"}, EndDate={">$(=Date(EndOfMonth(Max(MonthYear), -1)))"}>} DISTINCT MatriculeNumber)

Remember to adjust these expressions according to your actual field names, table names, and calendar setup.

Bar Chart Visualization:

  1. Add a bar chart to your sheet.
  2. Use the master calendar's "MonthYear" field as the dimension.
  3. Add the calculated expressions to the measures section of the chart using the expressions you created earlier.
https://www.linkedin.com/in/cristianjorge/
Do not forget to mark as "Accepted Solution" the comment that resolves the doubt.
glennmaldonado23
Contributor II
Contributor II
Author

Thanks for the answer but still not working. 

For the point number 1. 

Is the count of total MatriculeNumbers which Begin Date is less that the start of the MonthYear from Master Calendar and higher than the end of the Month Year from MasterCalendar.

There are 2 condition to be accomplished for the function Count. 

For example in Jan-2013, 3 Matricule numbers are on guaratee. That means that the three matricule numbers have  Begin Date < 01/01/2013 and End Date > 31/01/2013. 

marcus_sommer

In addition to the above hints you will need to resolve the Beginn to End time-span to a real date to be able to link this date to a calendar. The howto is described here:

IntervalMatch - Qlik Community - 1464547

glennmaldonado23
Contributor II
Contributor II
Author

Try to follow the example but getting loops and not the required outcome.  Activity is easy on excel but on qlik have being for 6 days stucked 

Fertigungsliste_02: 
LOAD
    "# - FL" as LokID_02,
    "Lok-Nr - FL" as LokNummer_02,
    Date("Ausgeliefert - FL", 'M/D/YYYY') as LiefDatum_02,
    Date("Q40 Ende - FL", 'M/D/YYYY') as Q40Datum_02, 
    Date("GWL-Beginn - FL", 'M/D/YYYY') as GWLBegin_02,
    Date("GWL-Ende - FL", 'M/D/YYYY') as GWLEnde_02,
    AutoNumberHash128("# - FL"&'|'&"Lok-Nr - FL") as KeyCalendario_02,
    "Halter - FL" as Holder_02, 
    "Kunde - FL" as Künde_02, 
    "Var. - FL" as Variante_02
    
FROM [lib://DataFiles/Datei_Quelle_probe.xlsx]
(ooxml, embedded labels, table is Fertigungsliste);
 
DataBridge_02: 
Load
KeyCalendario_02 as KeyCalendario_02
    ,GWLBegin_02 as Canonical_Date_DB_02 
    ,'GWLBegin_02' as FlagData_DB_02
    
Resident Fertigungsliste_02;
 
Load
KeyCalendario_02 as KeyCalendario_02
    ,GWLEnde_02 as Canonical_Date_DB_02 
    ,'GWLEnde_02' as FlagData_DB_02
    
Resident Fertigungsliste_02; 
 
QuartersMap_02:  
MAPPING LOAD   
rowno() as Month_02,  
'Q' & Ceil (rowno()/3) as Quarter_02  
AUTOGENERATE (12); 
 
Temp_02:  
Load  
date('1/1/2012', 'M/D/YYYY') as minDate_02,  
date('12/31/2012', 'M/D/YYYY') as maxDate_02  
Resident DataBridge_02;  
 
Let varMinDate_02 = Num(Peek('minDate_02', 0, 'Temp_02'));  
Let varMaxDate_02 = Num(Peek('maxDate_02', 0, 'Temp_02'));  
DROP Table Temp_02;  
 
TempCalendar_02:  
LOAD  
$(varMinDate_02) + Iterno()-1 As Num_02,  
Date($(varMinDate_02) + IterNo() - 1, 'M/D/YYYY') as TempDate_02  
AutoGenerate 1 While $(varMinDate_02) + IterNo() -1 <= $(varMaxDate_02);  
 
MasterCalendar_02:  
Load  
 
  TempDate_02 AS Canonical_Date_DB_02, 
  Year(TempDate_02) As Year_02,  
  Month(TempDate_02) As Month_02,  
  Day(TempDate_02) As Day_02,
  Date(MonthStart(TempDate_02), 'M/D/YYYY') as MonthYearStart_02, 
  Date(MonthEnd(TempDate_02), 'M/D/YYYY') as MonthYearEnd_02, 
  Date(TempDate_02, 'M/YYYY') as MonthYear_02,
  Date(MonthStart(TempDate_02,1), 'M/D/YYYY') as NextStart_02
  
Resident TempCalendar_02 
Order By TempDate_02 ASC;
Drop Table TempCalendar_02;
 
IntervalMatch: 
IntervalMatch(Canonical_Date_DB_02)
Load 
Distinct 
GWLBegin_02
,GWLEnde_02
Resident Fertigungsliste_02

 

 

marcus_sommer

The "classical" intervalmatch will create a synthetic-key between the fact-table and the intervalmatch-table which is officially regarded as ok. and working by design - see the last part of the blog-posting. But it's not mandatory to keep it in this way and many join it back again. I never do it - but I like the posting because of the explanation of the logic. Personally I use internal while-loops to create the real date.

Beside of this I don't think that's your main-issue else it's your date-bridge which didn't really fit. Both approaches seems to aimed to do the same but one is too much - respectively the intervalmatch-results should be created within  the date-bridge. Maybe with something like this:

bridge:
load Key, date(Beginn + iterno() - 1) as Date
resident X while Beginn + iterno() - 1 <= Ende;  

glennmaldonado23
Contributor II
Contributor II
Author

so complicated... never will understand