topic Re: weekly liquidity in QlikView

weekly liquidity

Wed, 14 Jan 2015 12:04:41 GMT

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

Re: weekly liquidity

Gysbert_Wassenaar — Wed, 14 Jan 2015 13:54:36 GMT

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
;

Re: weekly liquidity

Wed, 14 Jan 2015 17:26:50 GMT

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.

Re: weekly liquidity

Gysbert_Wassenaar — Thu, 15 Jan 2015 08:00:18 GMT

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

Re: weekly liquidity

Fri, 16 Jan 2015 14:07:13 GMT

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

thank you

Re: weekly liquidity

anbu1984 — Fri, 16 Jan 2015 14:09:41 GMT

Can you post entire script

Re: weekly liquidity

Fri, 16 Jan 2015 15:28:06 GMT

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

Re: weekly liquidity

Gysbert_Wassenaar — Fri, 16 Jan 2015 15:34:58 GMT

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.

Re: weekly liquidity

Fri, 16 Jan 2015 16:36:25 GMT

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

Re: weekly liquidity

Gysbert_Wassenaar — Fri, 16 Jan 2015 16:46:43 GMT

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;

Re: weekly liquidity

Mon, 19 Jan 2015 11:04:39 GMT

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

Re: weekly liquidity

Mon, 19 Jan 2015 16:05:53 GMT

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

Re: weekly liquidity

Mon, 19 Jan 2015 16:14:53 GMT

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....

Re: weekly liquidity

Gysbert_Wassenaar — Mon, 19 Jan 2015 17:11:51 GMT

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

Re: weekly liquidity

Tue, 20 Jan 2015 11:04:19 GMT

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

Re: weekly liquidity

Gysbert_Wassenaar — Wed, 21 Jan 2015 08:26:10 GMT

See attached qvw.

Re: weekly liquidity

Wed, 21 Jan 2015 16:59:14 GMT

Dear Gysbert,

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

Thanks a lot and all the best!