topic Re: How to Calculate Jira Stock with QlikSense in App Development

How to Calculate Jira Stock with QlikSense

pierre4 — Thu, 21 Nov 2024 08:46:46 GMT

Hello,

New to Qliksense, I want to calculate a stock of jira tickets.
In order to have a single time axis for the charts, I created a DateBrdige table.
The CanonicalCalendar table almost all the fields concerning the dates. I have only put a few fields here.

My relational schema is currently of the following form.

I now want to calculate the stock each month between the creation date and the resolution date.
After analysis I realized that I needed a DateBridge table of the following form:

Being new to Qlik, I don't see how to design this new DateBridge table.

Can you help me please?

Here's an example of my current code:

Jira: LOAD Projects.key , Issues.id as Id, Issues.key as Cle, Date#(Date(Issues.fields_created, 'DD/MM/YYYY')) as Created , Date#(Date(Issues.fields_resolutiondate, 'DD/MM/YYYY')) as Resolved , Issues.fields_reporter_displayName as Rapporteur, Issues.fields_issuetype_name as Type, Issues.fields_priority_name as Priorite FROM [lib://$(v_Dossier)/JIRA_Issues.qvd] (qvd) ; DateBridge: Load Cle, Created as CanonicalDate, 'Created' as EtatTicket Resident Jira ; Concatenate Load Cle, Resolved as CanonicalDate, 'Resolved' as EtatTicket Resident Jira ; CanonicalCalendar: LOAD Calendrier_ID, Date#(Date(Calendrier_DATE, 'DD/MM/YYYY')) as CanonicalDate , DayName, Week, Month, MonthEnd(Calendrier_DATE) as EndMonth FROM [lib:Dim_Calendrier.qvd] (qvd) WHERE Exists(CanonicalDate,Date#(Date(Calendrier_DATE, 'DD/MM/YYYY'))) ;

Re: How to Calculate Jira Stock with QlikSense

Qrishna — Thu, 21 Nov 2024 16:10:38 GMT

Sample data and expected output pls?

Re: How to Calculate Jira Stock with QlikSense

Kushal_Chawda — Thu, 21 Nov 2024 23:21:23 GMT

@pierre4 checkout my solution here

Re: How to Calculate Jira Stock with QlikSense

pierre4 — Mon, 25 Nov 2024 08:59:03 GMT

Finalement j'ai trouvé la solution suivante :

DateBridge: //CreationTickets: LOAD Cle, ddCreation AS CanonicalDate, 'Creation' AS EtatTicket RESIDENT Jira; CONCATENATE //ResolutionTickets: LOAD Cle, ddResolution as CanonicalDate, 'Resolu' as EtatTicket RESIDENT Jira WHERE flagResolu = 'resolu'; CONCATENATE //StockTickets: LOAD Cle, date(AddMonths(ddCreation, IterNo()-1) ,'dd/MM/yyyy')as CanonicalDate, 'Stock' as EtatTicket RESIDENT Jira WHILE AddMonths(MonthStart(ddCreation), IterNo()-1) <= If(IsNull(ddResolution), Date(Today(),'dd/MM/yyyy'), Date(AddMonths(MonthStart(ddResolution), -1))); ;

merci pour votre aide