Using Two date fields

[QV_learner]

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

[PrashantSangle]

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

Regards,
Prashant Sangle

[jyothish8807 · Accepted solution]

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.

[QV_learner]

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

[QV_learner]

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

Regards,

Vivek

[jyothish8807]

you are welcome 🙂