topic Re: Column reference calculation in pivot tables as Excel, help! in QlikView

Column reference calculation in pivot tables as Excel, help!

Mon, 03 Jun 2013 13:10:07 GMT

Hi!

Can someone help me with the following problem.
I have a pivot table where the formula needs to be adjusted.
The formula referenced on calculated columns.
How do I get the formula correctly adjusted so that the result corresponds to the attached Excel (column Set and the Dayend - correct formula).

Furthermore, the calculation for the column Dayend should be stored when I select the month 2 in the Listbox.

For example when I select the second month then the Dayend is to be taken from the first month -> last day from first month

(Dayend before = -2443 for the Day 31.01.2012).

Thanks for your help.

Screenshot from Excel:

Re: Column reference calculation in pivot tables as Excel, help!

RedSky001 — Mon, 03 Jun 2013 15:01:52 GMT

See the attached file. I've added two new expresisons. The labels are in bold.

Set (correct formula)

Set (correct formula) - Is

Dayend (correct formula)

if(isnull([Set (correct formula)]),0,[Set (correct formula)]) - Is

Re: Column reference calculation in pivot tables as Excel, help!

Tue, 04 Jun 2013 07:23:58 GMT

Hi Mark!

Thank you for your help that ist what I need, but I miss values on the month break!. How I can get this values?

Is it possible to hide the zero values of the rows when I use the inter record function (above etc...)

See attached screenshot.

Thank you!

Re: Column reference calculation in pivot tables as Excel, help!

RedSky001 — Tue, 04 Jun 2013 13:02:23 GMT

See the attached file, not exactly straghtforards and probaly would have been better to do this in the script...

Mark

Re: Column reference calculation in pivot tables as Excel, help!

Tue, 04 Jun 2013 13:40:28 GMT

Hi Mark!

This is a great solution, but when I select the 2nd month then the set and dayend column loses the previously calculated values.

This means the dayend is based on a different value then before.

See attached file.

How can I aggregate these values so that they do not get lost if you select something!

Thanks

Re: Column reference calculation in pivot tables as Excel, help!

RedSky001 — Tue, 04 Jun 2013 14:46:38 GMT

To do this you'll need to use set analysis and remove the selections you don't want (probably all of your dimensions)

sum({$<Ma_Networkdays=,GL$SYS_Gruppe=,Ma_Jahr_Monat=,Ma_Networkdays = Datum_GLSTRT>} GLPQOR/1000)

The problem is you can't do the equivilent of "Ma_Networkdays = Datum_GLSTRT" in set analysis to reduce the data set. I don't understand your data model but you really should avoid having to do such expressions in the script.