topic Weekly Opening and Closing(Outstanding)Balance calculation in QlikView

Weekly Opening and Closing(Outstanding)Balance calculation

khan_imran — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Weekly Opening and Closing(Outstanding)Balance calculation

sunny_talwar — Thu, 22 Sep 2016 19:32:00 GMT

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]

Re: Weekly Opening and Closing(Outstanding)Balance calculation

khan_imran — Fri, 23 Sep 2016 05:45:17 GMT

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

Re: Weekly Opening and Closing(Outstanding)Balance calculation

sunny_talwar — Fri, 23 Sep 2016 15:12:48 GMT

Try replacing your first expression to this:

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

Re: Weekly Opening and Closing(Outstanding)Balance calculation

sunny_talwar — Fri, 23 Sep 2016 15:14:15 GMT

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

Re: Weekly Opening and Closing(Outstanding)Balance calculation

khan_imran — Sat, 24 Sep 2016 08:57:20 GMT

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

Thanks very much.

Re: Weekly Opening and Closing(Outstanding)Balance calculation

sunny_talwar — Sat, 24 Sep 2016 20:19:52 GMT

I am glad it did

Best,

Sunny