Weekly Opening and Closing(Outstanding)Balance calculation

[khan_imran]

Hello All,

I am having a requirement to figure out Opening and closing(Outstanding) balance on a weekly basis. I have list of tickets where Created and resolved date is available. I want to evaluate Opening balance for a week and closing(Outstanding) at the end of the week.

In this scenario, My Outstanding at the end of the week will become Opening balance for next week. Here, I have created week on the basis of created date and my week starts from Monday till Saturday,

Please refer the attached excel and i need a report in the same format.

Do let me know if you all have any queries.

Regards,

Imran Khan

[sunny_talwar]

Is this what you want?

Script

Table:

LOAD Ticket,

     [Created Date],

     [Resolved Date]

FROM

[Sample Data (2).xls]

(biff, embedded labels, table is [Sample data$]);

MinMax:

LOAD Min([Created Date]) as MinDate,

  Max([Resolved Date]) as MaxDate

Resident Table;

LET vMin = Peek('MinDate');

LET vMax = Peek('MaxDate');

DROP Table MinMax;

Calendar:

LOAD Date,

  WeekEnd(Date) as WeekEnding;

LOAD Date($(vMin) + IterNo() - 1) as Date

AutoGenerate 1

While $(vMin) + IterNo() - 1 < $(vMax);

Left Join (Table)

IntervalMatch(Date)

LOAD [Created Date],

     [Resolved Date]

Resident Table;

Straight Table

Dimension

WeekEnding

Expressions

Alt(Above([Outstanding for the week]), 0)

Count(DISTINCT If(WeekEnding - 7 <= [Created Date], Ticket))

Count(DISTINCT Ticket)

Count(DISTINCT If(WeekEnding >= [Resolved Date], Ticket))

[Total for the week] - [Closed for the week]

[khan_imran]

Thanks Sunny. It is working fine. However, I am not getting Opening balance for first week of a month. For example, If i select Month as July and my first week is coming as 27Jun to 2Jul, then I should get an opening balance from week ending 25th or 26th June.

Could you please suggest how could i bring opening balance for my week of a month from closing balance of last week of a month?

Regards,

Imran Khan

[sunny_talwar]

Try replacing your first expression to this:

Alt(Above(Count({1}DISTINCT Ticket) - Count({1}DISTINCT If(WeekEnding >= [Resolved Date], Ticket))), 0) * Avg(1)

[sunny_talwar · Accepted solution]

Or may be this:

Alt(Above(Count({<WeekEnding>}DISTINCT Ticket) - Count({<WeekEnding>}DISTINCT If(WeekEnding >= [Resolved Date], Ticket))), 0) * Avg(1)

Depending on what selections you want to ignore. All or just WeekEnding

[khan_imran]

Thanks Sunny for giving the perfect solution for my requirement. It worked perfectly.

Thanks very much.

[sunny_talwar]

I am glad it did

Best,

Sunny