Date interval

[glennmaldonado23]

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. 

[cristianj23a]

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.

[glennmaldonado23]

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]

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]

so complicated... never will understand