Handling %

[#1]

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

[its_anandrjs]

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

[#3]

Hi Emac,

please find the attachment

[MK_QSL]

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

[MarcoWedel · Accepted solution]

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

[rwunderlich]

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

[rwunderlich]

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:

10

20

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

[MarcoWedel]

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