topic Handling % in QlikView

Handling %

Tue, 16 Dec 2014 18:14:45 GMT

Hello,

How do i achieve this? I created a drill down control having Year and Month

IF (dimension = Year) //do this

sum(Actual)/12

else

(dimension = Month)

sum(Actual)

How do i write this in Qlikview

Find attached

Thank You

Re: Handling %

its_anandrjs — Tue, 16 Dec 2014 18:29:13 GMT

Hi,

In the expression for the Actual expression write following.

if(GetSelectedCount(Year)=1,sum(Actual)/12,sum(Actual))

And in conditional write

GetSelectedCount(Year)=1

Regards

Anand

Re: Handling %

Tue, 16 Dec 2014 18:40:28 GMT

Hi Emac,

please find the attachment

Re: Handling %

MK_QSL — Tue, 16 Dec 2014 19:49:26 GMT

=IF($(=GetCurrentField(mYear))='Year',SUM(Actual)/12,SUM(Actual))

Re: Handling %

MarcoWedel — Tue, 16 Dec 2014 21:09:40 GMT

Hi,

as sum(Actual)/12 actually calculates the monthly average for the year dimension, you might as well change your expression to simply

Avg(Actual)

regardless of the selected drill down dimension.

Doing so would also enable your users to focus e.g. on specific quarters or months and still get correct averages for the selected/selectable months:

hope this helps

regards

Marco

Re: Handling %

rwunderlich — Tue, 16 Dec 2014 21:56:40 GMT

I don't think you need an if() test. Just write your expression as:

sum(Actual) / Count(DISTINCT Month)

When your group is at the Year level, DISTINCT Month will be 12. When you are at the Month level, the value will be 1.

-Rob

Re: Handling %

rwunderlich — Tue, 16 Dec 2014 22:09:14 GMT

Marco,

I think the avg() technique only works when there is a single value for the month. The distinction is whether the user wants an average of all values, or the avg monthly sum. For example, if there are two values for August 2014:

avg(Value) = 15.

sum(Value) / count(DISTINCT Month) = 30.

(I notice in the OP's data, there was only one value per month. But I thought I would point out the pitfall in case he had provided a simple example)

-Rob

Re: Handling %

MarcoWedel — Tue, 16 Dec 2014 22:15:26 GMT

Yes, that's true. There is a difference between this two expressions and it depends on the requirements which one of them fulfills the needs.

thanks

regards

Marco