topic Re: Two expressions - for the second, show only totals in QlikView

Two expressions - for the second, show only totals

salto — Mon, 17 Nov 2014 11:56:25 GMT

Hello,

I have a pivot table in which I show the P&L for the current year, and for the previous year, per month:

Ok, not bad until now. The requirement is that I would need the '2013' column to show only the totals, not the values for every month. The % variation ONLY for the totals would also be fantastic to have...

Is this possible? Many thanks in advance!

Re: Two expressions - for the second, show only totals

kristoferahlin — Mon, 17 Nov 2014 12:06:43 GMT

Unfortunately, I think the only way is to create one expression per month, one expression for 2013, one for 2014 and one for variation (%).

I have been wrestling with the same problem myself and look forward to being proven wrong

Cheers,

Kristofer

Re: Two expressions - for the second, show only totals

salto — Mon, 17 Nov 2014 14:09:55 GMT

Thank you Kristofer, will do it with the workaround you have suggested.

Cheers!

Re: Two expressions - for the second, show only totals

Anonymous — Mon, 17 Nov 2014 21:20:34 GMT

How about setting the expression text colour to white on a white background with a suitable expression using set analysis, an if statement and the dimensionailty() function.

Re: Two expressions - for the second, show only totals

salto — Tue, 18 Nov 2014 07:56:09 GMT

Thank you Bill.

I had a look at the dimensionality() function but in my chart, for every month and for totals, the value of dimensionality() returns 1 in all cases.

And, even if the dimensionality functions returned a different value (let's say,1 for totals and 2 for every month), would it be possible to hide the whole columns in which dimensionality returns 1 (including the heading and not only the values), instead of changing the text colour and background to white?

Many thanks again for your suggestion.

Re: Two expressions - for the second, show only totals

tresB — Tue, 18 Nov 2014 08:03:25 GMT

Not sure if understood right. If you want same total across months, try with 'Total' something like: Sum( Total Amount) . If that doesn't help, try to share your expected output.

Re: Two expressions - for the second, show only totals

salto — Tue, 18 Nov 2014 08:21:24 GMT

Hello tresesco,

this is what I had, using Description and Month as Dimensions, and two Expressions:

The expressions are:

=num(sum({<Year={$(=max(Year))}>} MonthlyAmount), '#.##0 $(vCurrency)')

and

=num(sum({<Year={$(=max(Year)-1)}>} MonthlyAmount), '#.##0 $(vCurrency)')

I would like, for the second expression, to show only the totals.

I managed to do it removing the Month field from the Dimensions, and adding :

a expression for every month in max(Year),
other expression for Totals in max(Year),
other for Totals in max(Year)-1
and other one for Var(%).

This is what I wanted and what I got, following Kristofer's suggestion:

It works, but I had to play a bit with getselectedfields(Month).

The question was if this could be directly achieved without adding a expression per month, making the second expression to show only the totals.

Regards!