QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

New Contributor

Show YTD as total column

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?

1 Solution

Accepted Solutions
Partner

Re: Show YTD as total column

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.

8 Replies
Partner

Re: Show YTD as total column

try with total

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

New Contributor

Re: Show YTD as total column

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.

Partner

Re: Show YTD as total column

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

New Contributor

Re: Show YTD as total column

Thanks for the quick replies, much appreciated!

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

Partner

Re: Show YTD as total column

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.

New Contributor

Re: Show YTD as total column

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?

Partner

Re: Show YTD as total column

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)`
New Contributor

Re: Show YTD as total column

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