topic Re: Pivot table dimensionality in App Development

Pivot table dimensionality

eagle__pedro — Tue, 05 Oct 2021 15:12:42 GMT

Hi,

I have a problem with calculating some dimensions.

I have four dimensions: WBS,job name, Task, Person.

I have the correct value for sheduled hours in WBS, job name, Task, Person and i have the correct value for effective hours in WBS.

I have to calculate the effective hours on the other dimensions by calculating the percentage of hours planned first on each job name within the wbs and then for each task based on the previous calculation

example

To calculate the effictive hours i used this formula

if(dimensionality()=2,
sum(sheduled_hours)/sum(total(sheduled_hours))*(aggr(sum(total([effective_hours]))+),WBS)),
if(Dimensionality()=3,
sum(sheduled_hours)/sum(total(sheduled_hours))*(aggr(sum(total([effective_hours]))),WBS)),
if(Dimensionality()=4,
sum(sheduled_hours)/sum(total(sheduled_hours))*(aggr(sum(total([effective_hours]))),WBS)))
))

my problem is that according to the filter that is used the value of the effective hours varies because the value of the percentage of planned hours varies.
How can I always have the same data?

Re: Pivot table dimensionality

eagle__pedro — Tue, 05 Oct 2021 15:35:04 GMT

sorry, i used

if(Dimensionality()=2, sum(sheduled_hours)/sum(Total(sheduled_hours)),
if(Dimensionality()=3, sum(sheduled_hours)/sum(Total(sheduled_hours)),
if(Dimensionality()=4, sum(sheduled_hours)/sum(Total(sheduled_hours)))))
*(sum(total([effective hours])))

Re: Pivot table dimensionality

SBN — Tue, 18 Apr 2023 12:00:06 GMT

Hi there,

Did you manage to solve this?
I have similar question in the following thread:
https://community.qlik.com/t5/App-Development/Qlik-Sense-Pivot-Table-Calculate-percentage-of-previous-row/m-p/2061356

Kind regards,

SBN