topic Re: challenging Request in QlikView

challenging Request

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

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

Re: challenging Request

sunny_talwar — Fri, 13 May 2016 18:18:58 GMT

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

Re: challenging Request

alec1982 — Fri, 13 May 2016 18:35:58 GMT

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

Re: challenging Request

sunny_talwar — Fri, 13 May 2016 18:38:26 GMT

Can you update the Excel also?

Re: challenging Request

alec1982 — Fri, 13 May 2016 18:58:30 GMT

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

Re: challenging Request

sunny_talwar — Fri, 13 May 2016 19:20:13 GMT

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

Re: challenging Request

alec1982 — Fri, 13 May 2016 19:25:28 GMT

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?

Re: challenging Request

sunny_talwar — Fri, 13 May 2016 19:40:30 GMT

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

Re: challenging Request

Fri, 13 May 2016 20:38:26 GMT

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)

Re: challenging Request

Fri, 13 May 2016 20:39:37 GMT

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

Re: challenging Request

sunny_talwar — Fri, 13 May 2016 20:42:46 GMT

Yes, I did not go into the the nitty-gritty hoping that Alec will be able to get that resolved. I focused on getting the numbers right. But you are right, Alec might need to make changes based on his final requirement.

Re: challenging Request

alec1982 — Fri, 13 May 2016 21:22:45 GMT

while this works well in QlikView and is greatly appreciated. I have to do it in parallel in QlikSense but it doesn't work.

Any one has experience in Sense?

Re: challenging Request

alec1982 — Sat, 14 May 2016 02:05:03 GMT

I will go ahead and post it on Sense community as well.

Re: challenging Request

sunny_talwar — Sat, 14 May 2016 11:46:00 GMT

Sure thing, in the meantime I would suggest to close this thread by marking any correct and helpful answers since you have moved the discussion to a new thread now.

Best,

Sunny

Re: challenging Request

alec1982 — Sat, 14 May 2016 13:15:44 GMT

Will do. give me few hours to find a solution and I will close this one as solved by you. Thanks for all the help.

Re: challenging Request

Anonymous — Sat, 14 May 2016 13:22:26 GMT

take the pivot table and select the pivot table and taken date and expression is sum (field name) and after

that right on that on the table select than change into horziontally

Re: challenging Request

alec1982 — Sat, 14 May 2016 21:14:14 GMT

thank you.