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

[Gysbert_Wassenaar]

Maybe something like this:

Ledger:
LOAD
    ACCOUNT,
    BANK,
    VALUE_DATE,
    CURRENCY,
    COMPANY,
    AMOUNT,
    TOT_EURO,
    TOT_USD,
    Dual(WeekYear(VALUE_DATE)&'-'&Week(VALUE_DATE),WeekStart(VALUE_DATE)) as Week
FROM ....
;

WeekTotals:
LOAD
    Week,
    ACCOUNT,
    WeekSum,
    if(ACCOUNT=previous(ACCOUNT), RangeSum(Weeksum,peek('WeekBalance'))) as WeekBalance;
LOAD
    Week,
    ACCOUNT,
    sum(AMOUNT) as WeekSum
RESIDENT Ledger
GROUP BY ACCOUNT, Week
ORDER BY ACCOUNT, Week
;

[#3]

Dear Gysbert Wassenaar,

many thanks for your clear answer.

I tried your suggestion but when i try to reload i receive the following error:

Field not found - <ACCOUNT>
LOAD 
    Week,
    ACCOUNT,
    sum(AMOUNT) as WeekSum 
RESIDENT Ledger
GROUP BY ACCOUNT, Week
ORDER BY ACCOUNT, Week

it is related at the final LOAD where I have also tried to add the filed ACCOUNT in the latest LOAD section.

[Gysbert_Wassenaar]

Make sure to use the exact case sensitive field names. Qlikview is case sensitive with regards to field names.

[#5]

Thank you. I checked but the case field are correctly specified. Anything else that can be the cause?

thank you

[anbu1984]

Can you post entire script

[#7]

Dear all,

many thanks for your kind help. Finally I've sorted out the field not found as it was releated to the "week" alias that is a system name. Now the problem is that the balance sum seems incorrect. In particular my really need is to calculate the weekly balance grouped by division (field DIVISIONE), company (field SOCIETA), banca (field BANCA) and account (filed CONTO). I've revised the below script. ON the other way one of thing that could affect the sum is the field weekf that in case of months with 1 caracter doesn't add the "0" before the number  and this may cause uncorrect ordering (example 2015-1 instead of 2015-01). I'm not sure if this is can be a cause. Please help.

Thank you

Ledger:

LOAD IDSALDO,

     CONTO,

     BANCA,

     DATA_CONTABILE,

     VALUTA,

     SOCIETA,

     TOTALE,

     TOT_EURO,

     TOT_USD,

     DIVISIONE,

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

FROM

(qvd);

WeekTotals: 

LOAD 

    WeekF,

    CONTO,

    BANCA,

    SOCIETA,

    DIVISIONE,

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

LOAD

    CONTO,

    BANCA,

    SOCIETA,

    DIVISIONE,

    WeekF,

    sum(TOT_EURO) as WeekSum

RESIDENT Ledger

GROUP BY DIVISIONE, SOCIETA, BANCA, CONTO, WeekF

ORDER BY DIVISIONE, SOCIETA, BANCA, CONTO, WeekF

[Gysbert_Wassenaar]

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

My mistake. You should use the same field names here, only WeekBalance.

If you want a leading 0 in front of the months use num(Week(DATA_CONTABILE),'00') instead.

[#9]

Dear Gysbert,

sorry i'm not sure to have understood what you mean with:

QUOTE

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

My mistake. You should use the same field names here, only WeekBalance.

UNQUOTE

The leading 0 is now working but the balance is uncorrect. It seems that the order for the sum is not correct. As additional information I can see that my database cotains also the start balance amount for each account that I have added with the 01-01-1900 date and so it shows as 1900-01 in the field weekf. I think that this rows will be not used in the sun of the balance.

Where I wrong?

Thank you

[Gysbert_Wassenaar]

If your database contains a starting balance for each account then you'll have to add that value to the WeekBalance to get to correct balance.

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