Help regarding carryon count of tickets

[Anonymous]

Hi All,

in the attached sheet there are fields ticket number, created date and resolved date and ticket status

my requirement is

lets says total tickets raised in a month is 100 and 12 of them is resolved by last day of that month the carry forward should be 88 that has to be added to the tickets on the month start of next month and it has to go on

can we achieve something like that ,

Thanks in Advance

stalwar1‌kush141087‌gwassenaar‌swuehl‌maxgro‌franky_h79‌

[Anonymous]

i have to appreciate your patience with my silly questions still i have a doubt that 35 tickets i am showing there active tickets and if i take set analysis only for the month of june it is showing me 33 . usually the active tickets has to be same .i our scenario it should be 35 i believe

[swuehl]

I think the difference is cause by the fact that some tickets are in status active, but show a resolved date before today (even before the month of today, June):

ticket_id	ticket_status	createddate	resolveddate	Ticket_ResolveDate
193928	Active	08.04.2016	14.04.2016	14.04.2016
345724	Active	16.02.2016	19.05.2016	19.05.2016
206709	Active	13.06.2016	15.06.2016	15.06.2016
204790	Active	02.06.2016	28.06.2016
205323	Active	06.06.2016	28.06.2016
205547	Active	07.06.2016	28.06.2016
206308	Active	10.06.2016	28.06.2016
207162	Active	15.06.2016	28.06.2016
207373	Active	15.06.2016	28.06.2016
343563	Active	04.02.2016	28.06.2016
343890	Active	07.02.2016	28.06.2016
349779	Active	04.03.2016	28.06.2016
357198	Active	06.04.2016	28.06.2016
365952	Active	04.05.2016	28.06.2016
374811	Active	10.06.2016	28.06.2016
375956	Active	15.06.2016	28.06.2016
375968	Active	15.06.2016	28.06.2016
512279	Active	29.04.2016	28.06.2016
513139	Active	02.05.2016	28.06.2016
526337	Active	29.05.2016	28.06.2016
526873	Active	31.05.2016	28.06.2016
527498	Active	01.06.2016	28.06.2016
528859	Active	05.06.2016	28.06.2016
529251	Active	06.06.2016	28.06.2016
533326	Active	14.06.2016	28.06.2016

The first two tickets should explain the difference of two.

[#4]

Thanks for this, really helped!

How would I leave weekends out of the reference date caculations?

LinkTable:

LOAD *,

MonthName(ReferenceDate) as ReferenceMonthName,

WeekName(ReferenceDate) as ReferenceWeekName,

WeekDay(ReferenceDate) as ReferenceWeekDAYName

WHERE ReferenceDate <= Today();

LOAD

  [Case Number],

  Date(createddate+iterno()-1) as ReferenceDate,

    If(iterno()=1, 1,0) as Flag_Created,

    If(Date(createddate+iterno()-1) = resolveddate, 1,0) as Flag_Resolved,

    If(createddate+iterno()-1 < resolveddate,1,0) as Flag_Open

Resident Cases

WHILE createddate+iterno()-1 <= resolveddate;

[swuehl]

Maybe like

...

WHERE ReferenceDate <= Today() and Weekday(Referencedate)<5; // to remove sat and sun