🧾 Deleted Checks → Audit Trail (SAFE)

Qlik Sense load script and demo app for building an audit trail of Issued → Deleted → Reissued checks with fiscal context and deposit rollups by JRNO. Ships with synthetic sample data and privacy-safe fields—no connections, secrets, or real customer data required.

---

✨ What you get

• Qlik script that transforms raw check activity into an event stream:

  • Events_Final (Issued / Deleted / Reissued with timestamps)
  • CheckSummary (per-check rollup)
  • DepositByJRNO / DepositSummary (deposit totals excluding deletes)

• Fiscal calendar join + period/quarter/year fields

• Reissue intelligence (prev voucher mapping, amount deltas, backdating flags)

• Masked payer fields option for safe sharing

• Demo dashboard layout notes + KPI expressions

---

🚀 Quick start (demo with CSVs)

1. Clone or download this repo.

2. In Qlik (Cloud, Server, or Desktop):

   • Create a Folder connection named Transfer pointing to:

     .../Deleted-Checks-Audit-Trail/sample/
     

   • (Optional) Create a Folder connection __TEMP__ for local STORE outputs.

3. Open or create an app, go to Data load editor, and paste the script from:

   src/DeletedChecks_AuditTrail_SAFE.qvs
   

4. Click Load data.

5. Build or import the dashboard sheet (see “Dashboard outline” below) and take a screenshot if you want to update the README image.

---

📦 Repo layout

/sample/
  CALENDAR_MASTER.csv         # fiscal calendar (daily rows)
  DeletedChecks.csv           # check activity (synthetic)
/src/
  DeletedChecks_AuditTrail_SAFE.qvs
LICENSE
README.md
SECURITY.md
.gitignore
assets/
  qlik-dashboard-preview.png  # <-- add your screenshot here

---

🧠 Data model (sample schema)

CALENDAR_MASTER.csv

Column	Example	Notes
%InvDate.Key	1/2/2024	calendar date (M/D/YYYY)
FiscalPeriod	2024-01	YYYY-MM
PeriodID	2024-01	YYYY-MM
FiscalYear	2024
FiscalQuarter	Q1	Q1..Q4
Date	1/2/2024	duplicate of key for demos

DeletedChecks.csv

Column	Example
Company, Division, BankID	300, 100, 001
CheckNumber	CHK01000
Payer	10017
PaymentAmt	1234.56
VoucherSeriesNum, VoucherNum	001, 20000005
YearNum	2024
AccountingDate	20240305
Deletion_Date	1709769600
Original_Check_Creation_Date	1709251200
Reversal_Voucher_Num	20000001
JRNO, JSNO	132, 2
Status	Issued/Deleted/Reissued
CUNM	Acme Dental

Note: Epoch second columns may be blank for some rows. The script safely converts them to timestamps.

---

🧩 How it works (high-level)

1. Calendar: Load calendar CSV → derive %DateKey, FiscalPeriod/Quarter/Year.

2. Source: Load DeletedChecks.csv and normalize dates/amounts.

3. Reissue mapping: Use Reversal_Voucher_Num + series to link prior vouchers.

4. Events: Emit three event rows per check where applicable:

   • Issued (creation ts), Deleted (deletion ts), Reissued (new issue ts)

5. Flags: AmountChanged, BackdatedReissue, OrphanPrev.

6. Summaries: CheckSummary and DepositByJRNO/DepositSummary.

---

📊 Dashboard outline (what to build)

Sheet: Audit Trail — Overview

• KPIs

  • Checks Audited: Count(DISTINCT %CheckKey)
  • Net Amount: Num(Sum(CheckAmount),'$#,##0.00')
  • Reissued (count): Count({<AuditStep={'Reissued'}>} DISTINCT %CheckKey)
  • Deleted (abs $): `Num(Abs(Sum({<AuditStep={'Deleted'}>} CheckAmount)),'$#,##0.00')`
  • Reissue Rate: Num( Count(DISTINCT {<AuditStep={'Reissued'}>} %CheckKey) / Count(DISTINCT %CheckKey), '0.0%' )

• Bar: Event Breakdown

  • Dim: AuditStep; Meas: Sum(CheckAmount)

  • Color by expression:

    If(AuditStep='Issued',    RGB(33,158,188),
    If(AuditStep='Reissued',  RGB(144,190,109),
    If(AuditStep='Deleted',   RGB(230,57,70),
                               RGB(160,160,160))))
    

• Area/Line: Amount over Fiscal Period

  • Dim: Accounting_FiscalPeriod
  • Meas: Num(Sum(CheckAmount),'$#,##0')
  • Reference line at 0

• Top 10 by Net $ (Table)

  • Dim: PayerName (masked in public demos if desired)
  • Meas: Num(Sum(CheckAmount),'$#,##0')
  • Dimension limit → Top 10 (include “Others”)

• Filters: FiscalYear, FiscalQuarter, FiscalPeriod, AuditStep, BankID, Division, JRNO

Sheet: Reissue Analyzer (optional)

• KPIs: Sum(AmountChanged), Sum(BackdatedReissue), Sum(OrphanPrev)
• Table: PrevVoucherNum → VoucherNum with Prev_CheckAmount, CheckAmount, Prev_OriginalCreationTS, EventTS
• Chart: Backdated counts by Accounting_FiscalPeriod

---

🔒 Privacy & safety

• Use sample CSVs in /sample/ for public sharing (already synthetic).

• If you adapt for your environment, do not commit:

  • Connection strings/DSNs, QVD/QVF outputs, private data

• See SECURITY.md for guidance; .gitignore excludes common artifacts.

---

🛠 Troubleshooting

• Field not found on calendar imports → your CSV editor may add a BOM or tweak headers. This repo’s script uses positional loads for maximum compatibility.
• Locale issues with function separators → switch , to ; in Num() if your Qlik locale uses semicolons.
• No data shown → confirm your Transfer folder connection points to /sample/.

---

📄 License

MIT — see LICENSE. Attribution appreciated if you find this useful 🙌

---

🙌 Contributing

Issues and PRs welcome (typos, improvements, or additional demo visuals). Ideas: optional partial reissue chains, per-payer risk scores, or a Qlik story mode.

---