weekly liquidity

[#1]

Dear all,

I have a table with the following fields

     ACCOUNT,

     BANK,

     VALUE_DATE,

     CURRENCY,

     COMPANY,

     AMOUNT,

     TOT_EURO,

     TOT_USD

I need to create a table that shows on weekly bases the total liquiduty at bank where each week is the total of the week movements added to the total the previous week and so on.

Is there any function in qlik view that can allow me to do that?

Please help

Thank you

[#2]

Dear Gysbert,

many thanks for your help. I understand the sintax but unsure of what must replace the variable "StartBalanceOfAccount".

In general the same DB in order to have the balance progression as start balance I've added for each account a start balance row using as initial date (field DATA_CONTABILE) the date 01/01/1900 so it is converted as 1900-01 as yera-week.

Thank you

[#3]

Dear Gybsert,

to be more clear In my table I can see that selecting the weekF=1900-01 that contains only the row of starting balance of some of the accounts the Weekbalance column is empty. On the other way I have other accounts where the starting balance is 0 and I do not need to use it but also for them the last available week balance that should be the sum of all previous weekbalance for the same account and same currency (field valute) isn't correct. is something missing in the load script (oder or group by?). I also put togheter the full sfript for your review. Please help me

Ledger:

LOAD IDSALDO,

     CONTO,

     BANCA,

     DATA_CONTABILE,

     VALUTA,

     SOCIETA,

     TOTALE,

     TOT_EURO,

     TOT_USD,

     DIVISIONE,

     Dual(WeekYear(DATA_CONTABILE)&'-'&num(Week(DATA_CONTABILE),'00'),WeekStart(DATA_CONTABILE)) as WeekF

FROM

(qvd);

WeekTotals: 

LOAD 

  WeekF,

    CONTO,

    BANCA,

    SOCIETA,

    DIVISIONE,

    VALUTA,

  if(CONTO=previous(CONTO), RangeSum(WeekF, peek('WeekBalance'))) as WeekBalance;

LOAD

    CONTO,

    BANCA,

    SOCIETA,

    DIVISIONE,

    VALUTA,

    WeekF,

    sum(TOTALE) as WeekSum

RESIDENT Ledger

GROUP BY DIVISIONE, SOCIETA, BANCA, CONTO, VALUTA, WeekF

ORDER BY DIVISIONE, SOCIETA, BANCA, CONTO, VALUTA, WeekF

[#4]

Dear Gybsert,

it's me again. In order to better understand how many rows were excluded I tried to put a constaint "startBalance" for all cases out of the expression:

if(CONTO=previous(CONTO), RangeSum(WeekF, peek('WeekBalance')),'StartBalanceOfAccount') as WeekBalance;

and found a couple of rows not inclued that are not only the starting balance rows but also other where for the same account and weekf exists more the one row. I'm really unable to understand why....

[Gysbert_Wassenaar]

Please post a file with sample data that closely resembles your real source data.

[#6]

Dear Gysbert,

please find atatche the sample data.

following is the script I've used. Thank you very mutch for your help

Ledger:
LOAD IDSALDO,
CONTO,
BANCA,
DATA_CONTABILE,
VALUTA,
SOCIETA,
TOTALE,
DIVISIONE,
Dual(WeekYear(DATA_CONTABILE)&'-'&num(Week(DATA_CONTABILE),'00'),WeekStart(DATA_CONTABILE)) as WeekF
FROM

(qvd);

WeekTotals:
LOAD
WeekF,
CONTO,
BANCA,
SOCIETA,
DIVISIONE,
VALUTA,
if(CONTO=previous(CONTO), RangeSum(WeekF, peek('WeekBalance'))) as WeekBalance;

LOAD
CONTO,
BANCA,
SOCIETA,
DIVISIONE,
VALUTA,
WeekF,
sum(TOTALE) as WeekSum

RESIDENT Ledger
GROUP BY VALUTA ,CONTO, BANCA,SOCIETA,DIVISIONE,WeekF
ORDER BY VALUTA ,CONTO, BANCA,SOCIETA,DIVISIONE,WeekF

[Gysbert_Wassenaar · Accepted solution]

See attached qvw.

[#8]

Dear Gysbert,

it works perfectly!!!! I thank you very mutch for your kind assistance.

Thanks a lot and all the best!