topic Re: Show YTD as total column in QlikView

Show YTD as total column

rh205bip — Tue, 11 Dec 2018 09:38:59 GMT

Hi,

I hope somebody can help me with this problem.

Example data:

Name | Month | # Hours

X JAN 8

X FEB 8

I would like to have an extra column in this pivot table with # Hours (YTD). When i use this measure: Num((Sum({<Year={$(=Max(Year))}, Month={'<=$(=Max(Month))'}>}[# Hours]) the pivot table should look like this, when filtering on month FEB:

Month : FEB|2018

Name | # Hours | # Hours YTD

X 8 16

...but what i get is this:

Month : JAN|2018 FEB|2018

Name | # Hours | # Hours YTD Name | # Hours | # Hours YTD

X 0 0 X 8 16

I tried using calculated dimension with max month, but then i only get the FEB data, JAN excluded.

Does anybody knows how I can get the desired outcome?

Thanks in advance!

Re: Show YTD as total column

Anonymous — Tue, 11 Dec 2018 09:45:17 GMT

try with total

Sum(total {<Year={$(=Max(Year))}, Month=>}[# Hours])

Re: Show YTD as total column

rh205bip — Tue, 11 Dec 2018 09:52:09 GMT

Hi,

Yeah, i tried that also. But then i get the same YTD for every Employee (Name), because its the total of hours, but not the total per employee.

Re: Show YTD as total column

Anonymous — Tue, 11 Dec 2018 10:06:51 GMT

can you try " total <Employee>" instead of only total on Expression?

Re: Show YTD as total column

rh205bip — Tue, 11 Dec 2018 10:43:02 GMT

Thanks for the quick replies, much appreciated!

When i add total <Employee> the table suddenly shows all months, instead of the selected month.

Re: Show YTD as total column

avkeep01 — Tue, 11 Dec 2018 11:40:05 GMT

Hi,

Try aggregating over the name:

AGGR(Sum({<Year={$(=Max(Year))}, Month={"<=$(=Max(Month))"}>}[# Hours]),Name)

Then the result will be caculated in one field, per name.

Re: Show YTD as total column

rh205bip — Tue, 11 Dec 2018 12:08:44 GMT

Allright, we are getting close:D

AGGR(Sum({<Year={$(=Max(Year))}, Month={"<=$(=Max(Month))"}>}[# Hours]),Name)

Works like a charm, thanks. But now i need a second AGGR measure on # Projecthours..

When i do the following:

=AGGR(Sum({<Year={$(=Max(Year))}, Month={"<=$(=Max(Month))"}>}[# Hours]),Name)

AGGR(Sum({<Year={$(=Max(Year))}, Month={"<=$(=Max(Month))"}>}[# Projecthours]),Name)

I dont get the expected outcome..only a few are correctly filled others not showing at all.

I know that sum X + sum Y works like this, is AGGR different in this manner?

Re: Show YTD as total column

avkeep01 — Tue, 11 Dec 2018 12:11:41 GMT

Probably best to do it within the aggregation.

You could try:

=AGGR(Sum({<Year={$(=Max(Year))}, Month={"<=$(=Max(Month))"}>}[# Hours])
+ 
Sum({<Year={$(=Max(Year))}, Month={"<=$(=Max(Month))"}>}[# Projecthours]),Name)

Or even

=AGGR(Sum({<Year={$(=Max(Year))}, Month={"<=$(=Max(Month))"}>}[# Hours]+[# Projecthours]),Name)

Re: Show YTD as total column

rh205bip — Tue, 11 Dec 2018 12:42:54 GMT

Great! i will use the first one, because i need to use different fields in the set analysis.