topic Re: Balance and Movements in QlikView

Balance and Movements

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

Hello,

I'm sorry because my english isn't good.

Forgive me if what I ask is simple to solve, but I'm new and I do not have much practice with QV.

I have a table with the following information:

- Account

- Closing Date

- Balance

With one record for each account and not always all accounts have the same closing date.

and another table with those produced movements, with the following data:

- Account

- Date Movement

- Amount

Given that on a date (Date Movement) may have several records movements and that not all movements have time, I would get a table with the following data.

- Account

- Date

- Balance date (sum of all movements that date)

- Balance on that date

Keep in mind that the Closing Date is greater than or equal to all movement dates from that account.

I would appreciate your help very much.

Regards,

Ana

Re: Balance and Movements

praveen_prithiviraj — Mon, 07 Nov 2016 05:42:06 GMT

Can you provide us with some sample data and the expected output ?

Re: Balance and Movements

Anil_Babu_Samineni — Mon, 07 Nov 2016 05:59:01 GMT

It's Tricky to understand with my scramble data. But, I would read to recommend create one sample data and then send with us. So that, We may can help you.

We Can do by using this.

T1:

Load Account,

Closing Date,

Balance

From Source

Left Join(T1)

T2:

Load Account,

Date Movement,

Amount

From Source

Then tell me how Data model looks

And then Create one Straight Table

Account, Balance date as Dimensions

Expression for Balance date

1) Sum({<[Date Movement] = P([Date Movement]) >}Amount)

So, Here how you are calculating the Closing Date

Do you mean This

Sum({<[Closing Date] = {'>=' & Date([Date Movement],'DD/MM/YYYY')}>}Amount + Balance)

Note:- f Not, Please provide me the Sample Data Which demonstrates the Output.

Re: Balance and Movements

Anonymous — Mon, 07 Nov 2016 10:23:27 GMT

Hi Ana,

Can you share some sample data please?

Thanks,

Re: Balance and Movements

avicentbarcelo — Thu, 10 Nov 2016 15:56:00 GMT

An excel data example

Re: Balance and Movements

avicentbarcelo — Fri, 11 Nov 2016 05:21:37 GMT

Yes. I attach an excel