topic Re: Help with a Simple Graph to track Balance in QlikView

Help with a Simple Graph to track Balance

Tue, 23 Jul 2013 11:28:44 GMT

Hello !

I have a database of balance values, where each row has the balance for a given day, like this :

BDATE; BALANCE

4/1/2010; 74.016,72

6/1/2010; 74.538,97

7/1/2010; 74.452,66

8/1/2010; 74.749,34

9/1/2010; 74.789,94

11/1/2010; 74.789,94

13/1/2010; 74.805,88

14/1/2010; 74.879,12

5/2/2011; 89.529,14

6/3/2012; 90.589,14

Notice that some days are not tracked.

I need to build a line graph where I can see the balance trend. Also, need to view it sometimes on a daily view, sometimes per month and other times per year.

However, I can not use "sum" on the graph cause then I would have the sum of the balances of the month for instance. That's not correct. On a monthly view, I could have for instance the latest balance value on the month , or the average balance value, or even, the balance of the very first day of the month. The same concept when regarded to a year view of the values.

I have provided a sample kit, with data included, so that your tests can be easier.

Thanks in advance for your support and sorry for such a silly question !

Re: Help with a Simple Graph to track Balance

Gysbert_Wassenaar — Tue, 23 Jul 2013 11:36:06 GMT

See attached qvw. It uses a cyclic group to switch between date, month and year. And uses the firstsortedvalue function to get the balance amount.

Re: Help with a Simple Graph to track Balance

kaushiknsolanki — Tue, 23 Jul 2013 11:40:17 GMT

Hi,

I would tell you the logic, you try and let us know.

The logic is, you create a flag in script for each monthend date and one for yearend date.

Now when you are trying to show data say month wise balances then use that flag to show data as on that day only. and same for year end.

Regards,

Kaushik Solanki

Re: Help with a Simple Graph to track Balance

Eugeny_Ilyin — Tue, 23 Jul 2013 11:40:48 GMT

You need create new table.

Use it:

load, if(), peek()

Re: Help with a Simple Graph to track Balance

Tue, 23 Jul 2013 12:01:48 GMT

The problem is that at script level, I dont know when it's a month end.

Cause on the original data, I may not have the balance value for the end of month on the day that the month ends.

For instance, on January 2012, the month ends on 31th of January of course. But I may not have data values for that particular day.

So the other answer, from Mr Wassenaar, is more practical due to the firstsortedvalue function that "flows" over data in other to pick the correct value within the month (or year).

But I understood what you said. Many times I create new fields during data load. I call those fields "derivated" fields and also use a standard suffix name for them, like "_DER" (from DERivated field).

Thanks anyway !