compare periods in drill down

[erwinvink]

Hi all,

I have a problem with comparing periods in a chart with a drill down dimension. the situation is:

I've a column chart with a drill down dimension. The Drill down contains Year, Quarter, Month. The expression/measure is a simple count of the employees in a period: Count(Distinct(employee_id)).

This is working good. For example: in 2013 I've 600 employees and in 2014 i've 500 employees. I can drill down to Quarter and Month. So I've 1 expression per dimension label. So far so good.

Now I need in 2014 two expressions. the count of employees in 2014 and the count of employees in 2013. Or in Quarter i want compere Q3 with Q2 etc. etc. I know I can do this with addmonths -12, CYTD / LYTD etc. but than I can't use my DrillDown anymore. so my question is:

How can I make a comparison between current and last period where the period depends on the dimension in my drill down?

Thanks a lot

[rubenmarin]

Hi Erwin, if this helps, you can use GetCurrentField() to know wich dimension is active in the group:

=GetCurrentField([GroupName])

Maybe you can use:

If(GetCurrentField([GroupName])='Year', ....

If(GetCurrentField([GroupName])='Quarter'...

[giakoum]

you can use getcurrentfield to determine what dimension is currently used in the drill down and based on that create your expression maybe with an if statement

getcurrentfield (GroupName)

Returns the name of the currently active field in a group.

Example

getcurrentfield( myGroup )

[erwinvink · Accepted solution]

Thanks for taking time.

I forgot to mention that I use Qlik Sense. Almost every ScriptFunction from QV is working in QS but this one is not. So I can't use this solution.

There is a function almost similar to this, named "GetFieldSelections". But to use it I need a IF function with many nested IF's. So that's killing for the performance.

Now I used the Above Function. This works very well. Only the expression on the first dimension didn't work. So therefor I used a If(RowNo() = . The expression is:

If(RowNo()=1,  count(distinct(employee_id)) - new_employee_id,  Above(Count(distinct(employee_id))))