topic Re: Ignoring dimension in a straight table in QlikView

Ignoring dimension in a straight table

Thu, 04 Oct 2012 10:51:01 GMT

Hi guys,

I have data in the following form and would like to create a straight table which has the same fields (Code, Date and balance) but the balance field should have accumulated values (chronologically) .

Code	Date	Balance

R1720	31-Aug-12	20,971
R1720	03-Sep-12	12,994
R1720	04-Sep-12	-12,807
R1720	05-Sep-12	1,366
R1720	06-Sep-12	-15,303
R1720	10-Sep-12	2,931
R1720	18-Sep-12	-12,269
R1720	28-Sep-12	6,380
R1720	01-Oct-12	47
R1720	04-Oct-12	1,701

The straight table should look something like this. Please note the last row sums all the values because the date field is the highest.

Code	Date	Balance
R1720	31-Aug-12	20,971
R1720	03-Sep-12	33,965
R1720	04-Sep-12	21,158
R1720	05-Sep-12	22,524
R1720	06-Sep-12	7,221
R1720	10-Sep-12	10,152
R1720	18-Sep-12	-2,117
R1720	28-Sep-12	4,263
R1720	01-Oct-12	4,310
R1720	04-Oct-12	6,011

Would appreciate all the help i can get

Regards

Faisal

Re: Ignoring dimension in a straight table

Thu, 04 Oct 2012 10:54:43 GMT

I could make another a data island with the same data and linking it to the oringal through the code field. That way i could compare the date fields. However is there another way i could do this? like a simplier way of doing this?

Re: Ignoring dimension in a straight table

Anonymous — Thu, 04 Oct 2012 11:26:07 GMT

Hi,

Use the Above() function. Something like =Above(Column(3))+sum(Balance)

You may need to do it in two different columns, one with the row value and another with the accumulated value.

Jonathan

Re: Ignoring dimension in a straight table

Thu, 04 Oct 2012 11:30:38 GMT

There's an issue with that because i will be hiding rows where the balance field is <=0 but those values need to be incorporated in the sum.

Re: Ignoring dimension in a straight table

Gysbert_Wassenaar — Thu, 04 Oct 2012 11:38:28 GMT

See the attached qvw. One table is build up in the load script. The other uses an expression

Re: Ignoring dimension in a straight table

Thu, 04 Oct 2012 12:05:25 GMT

Gwassenaar,

What if there multiple codes?

Re: Ignoring dimension in a straight table

Gysbert_Wassenaar — Thu, 04 Oct 2012 12:36:55 GMT

In that case the load script version will not restart the cumulative sum on a new Code. The expression will restart the cumulative sum for each order. See updated attached qvw