topic Pivot Table and After Function in App Development

Pivot Table and After Function

alec1982 — Sat, 14 May 2016 02:10:02 GMT

hi All,

I have been discussing a challenging request on the QlikView Side. Now I need to do the same thing in Sense.

Attached is a QlikView and a QlikSense Document and both of them have the same pivot table. on QlikView the logic works fine but on Sense it doesnt.

my request is as follow

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)

Let me know if anyone knows how to handle it in Sense..

Re: Pivot Table and After Function

swuehl — Sat, 14 May 2016 12:38:40 GMT

I think there is a difference in that QlikSense doesn't calculate aggregations with TOTAL qualifier in your pivot table chart the same way as QlikView.

Try your pivot table chart with an expression

=Sum(TOTAL Value)

You only get the expression evaluated where both dates are the same (in QS), while QV shows the value in every cell.

Not sure why there is this difference (at least for this specific scenario), maybe mto‌ can shed some light on this.

(Maybe this is a known issue)

I tried some other approaches, but always struggled somewhere around this different behaviour.

(I think the approach with mostly duplicating your fact as Sunny suggested first in the other thread should work, but that's not what you want, right?)

Re: Pivot Table and After Function

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

hi Stephan,

agree , Sum(TOTAL Value) returns the values only for the matching dates. but tht doesn't help .

I am fine to duplicate the table knowing that it is the only solution but it didnt work for me either. Have you tried it? and if it worked for you, can you please send me a QVF sample.

Thanks for all the help.

Alec

Re: Pivot Table and After Function

alec1982 — Sat, 14 May 2016 13:18:24 GMT

Actually, It worked on my end.

Thanks,

Alec

Re: Pivot Table and After Function

alec1982 — Sat, 14 May 2016 13:32:25 GMT

while this works well for me.. the issue is when having multiple dimensions it doesnt work.. any idea what could be the reason?

Re: Pivot Table and After Function

sunny_talwar — Sat, 14 May 2016 13:39:42 GMT

I have been trying it with Qlik Sense as well, not sure why the TOTAL doesn't work. Have you tried the first alternative?

Re: Pivot Table and After Function

sunny_talwar — Sat, 14 May 2016 13:45:35 GMT

I know you did not like the second method, but that one works in QlikSense

Re: Pivot Table and After Function

alec1982 — Sat, 14 May 2016 13:57:02 GMT

hi Sunny,

the first solution is ok with me since it is the only option we have.

the issue is how do I handle multiple dimensions and facts available on the table.

Attached is a better sample of the data I have. I didn't include all the dimensions and facts I have on it but if I can get this to work then I can take care of it.

Re: Pivot Table and After Function

sunny_talwar — Sat, 14 May 2016 14:24:46 GMT

What is the expected output here?

Re: Pivot Table and After Function

alec1982 — Sat, 14 May 2016 15:03:14 GMT

same as we you have helped me with. I want to use column D and Column E for the pivot table. it doesnt work for me as I have many other columns..

Note: I am planning on having condition to show the results only if the user select a single value from column C and one value on column A.

Re: Pivot Table and After Function

swuehl — Sat, 14 May 2016 20:59:08 GMT

Not sure about your requirements, but maybe like attached, using a key for the dimensions?

Re: Pivot Table and After Function

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

Sunny has helped me on it and it is all working now. thank you both

Re: Pivot Table and After Function

Mahesh9833 — Fri, 21 Jul 2023 11:17:16 GMT

Hi, is there any way to remove hyphen/'-' in pivot table?