Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
QV_learner
New Contributor

Using Two date fields

Hello,

I have two date fields, 'DateofSubmission' and 'ResolvedDate' for issues at center. I want to work on number of total issues submitted and total issues resolved. 

1. So, I created months and years using 'Dateofsubmission' field. It properly showed total issues submitted; but when I checked for issues resolved, it showed different numbers.

2. As a solution, I also created Master calendar; but now it is showing both numbers incorrect.

 

I want to create months and years so that both the numbers, issues submitted and issues resolved, will be shown properly.

Please, suggest the solution for the same!

Thanks and Regards,

Vivek

1 Solution

Accepted Solutions
jyothish8807
Honored Contributor II

Re: Using Two date fields

Hi Vivek,

One way could be to break the table into two:

MainTable:

Load

TicketID,

DateSubmitted,

Dateresolved

from <>;

Noconcatenate

SubmitTable:

Load

TicketID,

DateSubmitted as Date

1 as Flag

resident MainTable;

conccatenate

Load

TicketID,

Dateresolved as Date,

2 as Flag

resident MainTable;

Drop table MainTable;

 

Now you can create expression using the flag and create master calendar if required.

Count({<Flag={1}>}TicketID) // submitted

Count({<Flag={2}>}TicketID) // resolved

 

There can be other approaches also.

Hope it helps.

Best Regards,
KC
5 Replies

Re: Using Two date fields

How did you create Master calendar? From which field? can you share your script and expression which you are trying?

Regards,
Prashant Sangle
Great dreamer's dreams never fulfilled, they are always transcended.
jyothish8807
Honored Contributor II

Re: Using Two date fields

Hi Vivek,

One way could be to break the table into two:

MainTable:

Load

TicketID,

DateSubmitted,

Dateresolved

from <>;

Noconcatenate

SubmitTable:

Load

TicketID,

DateSubmitted as Date

1 as Flag

resident MainTable;

conccatenate

Load

TicketID,

Dateresolved as Date,

2 as Flag

resident MainTable;

Drop table MainTable;

 

Now you can create expression using the flag and create master calendar if required.

Count({<Flag={1}>}TicketID) // submitted

Count({<Flag={2}>}TicketID) // resolved

 

There can be other approaches also.

Hope it helps.

Best Regards,
KC
QV_learner
New Contributor

Re: Using Two date fields

Thanks, I tried the same; but it's still showing wrong values.

QV_learner
New Contributor

Re: Using Two date fields

Sorry, I didn't appropriately focus on Creation of Master Calendar. The problem is solved now.
Thank you so much Jyothish!

 

Regards,

Vivek

jyothish8807
Honored Contributor II

Re: Using Two date fields

you are welcome 🙂

Best Regards,
KC