challenging Request

[alec1982]

hi all,

I have a challenging request to create a pivot table as show in my attached excel file

the source is the upper table which looks like

5/18/2016	test	$ 254.00
6/15/2016	test	$        200.00
7/20/2016	test	$        500.00
8/17/2016	test	$        300.00

and the result table is as follow

Date	5/18/2016	6/15/2016	7/20/2016
5/18/2016	$      (54.00)	$        246.00	$     46.00
6/15/2016		$        300.00	$   100.00
7/20/2016			$ (200.00)

I have attached the excel file to show how i calculate the numbers..

I couldn't figure out a way to do it in QlikView so any help is appreciated

[sunny_talwar]

Can you recheck the calculation on the Excel. Something doesn't make sense about the calculation. It is referring to empty cells on occasions.

[alec1982]

thanks for letting me know.. I have fixed them..

[sunny_talwar]

Can you update the Excel also?

[alec1982]

It has been fixed.. sorry for some reason didnt get updated earlier.

[sunny_talwar · Accepted solution]

Have a look at the attached

Script:

Table:

LOAD *,

  Date as Date_;

LOAD * Inline [

Date, Dim, Value

5/18/2016, test, 254

6/15/2016, test, 200

7/20/2016, test, 500

8/17/2016, test, 300

];

Table2:

LOAD Date as _Date,

  Dim,

  Value as _Value

Resident Table;

Dimension

_Date

Date

Expression:

=If(Date >= _Date, After(Sum(Value)) - Sum(_Value))

[alec1982]

hi Sunny,

this is exactly what I am looking for as a sample. the problem is the data model is complex and has more columns on the table.. Is there anyway to do it in one table instead of using the two tables?

[sunny_talwar]

Another method:

Table:

LOAD *,

  Date as Date_;

LOAD * Inline [

Date, Dim, Value

5/18/2016, test, 254

6/15/2016, test, 200

7/20/2016, test, 500

8/17/2016, test, 300

];

Expression:

If(Date<= Date_, After(Sum(TOTAL <Date_> Value)) - Sum(TOTAL <Date> Value))

[#9]

You can modify the number format to convert -ve numbers into ().

Proerties --> Number --> select expression on left side, select Integer on right side and use this: #,##0;(#,##0)

[#10]

You don't need another table. You can add _Value into same table.