Pick Function QlikSense

[satishkurra]

Hi Team

I'm trying to use Pick Function as below. 

My Variable is vPostService. It has values as either 1 or 2

Post Date and Service Date are my dimensions.

avg(aggr(sum(Sales), Pick(vPostService,PostDate,ServiceDate)))

Appreciate the help

Tried vPostService as $(vPostService) and no luck

[Vegar]

I see what you are trying to do. I have not seen this pattern before.

The issue here is that in your pick() expects an string input, not a field reference. Try this adjustment:

avg(aggr(sum(Sales), Pick($(vPostService),'PostDate','ServiceDate')))

Or this:

avg(aggr(sum(Sales), [$(=Pick($(vPostService),'PostDate','ServiceDate'))] ))

[satishkurra]

Hi @Vegar 

Tried both options and no luck

[Kushal_Chawda]

@satishkurra  try like below

=pick(vPostService,
avg(aggr(sum(Sales), [Post Date])),
avg(aggr(sum(Sales), [Service Date])))

[satishkurra]

Hi @Kushal_Chawda , Tried and no luck

[Kushal_Chawda]

@satishkurra  can you send screenshot of your expression? In which chart you are using this expression? Make sure that variable always holds any values if it has null value expression will not work.

[satishkurra]

Sure.

Dimension: =pick(vPostService,[Post Date],[Service Date])

Expression: Sum(Sales)

Reference Line: avg(aggr(sum(Sales), [Post Date])) + 3*stdev(aggr(sum(Sales), [Post Date]))

For Reference Line expression, I'm trying to Switch with a button to change the dimension between Post Date and Service Date like I entered in the Dimension. It is displaying the chart but the reference line is not showing up.

Please let me know if this info helps

[Kushal_Chawda]

@satishkurra try below in reference line

avg(total aggr(sum(Sales), [Post Date])) + 3*stdev(total aggr(sum(Sales), [Post Date]))

[Kushal_Chawda · Accepted solution]

@satishkurra  or with below pick in reference line expression

pick(vPostService,

avg(total aggr(sum(Sales), [Post Date])) + 3*stdev(total aggr(sum(Sales), [Post Date]),

avg(total aggr(sum(Sales), [Service Date])) + 3*stdev(total aggr(sum(Sales), [Service Date])))

[Kushal_Chawda · Accepted solution]

avg(total aggr(sum(Sales), [Post Date])) + 3*stdev(total aggr(sum(Sales), [Post Date]),

avg(total aggr(sum(Sales), [Service Date])) + 3*stdev(total aggr(sum(Sales), [Service Date])))

[Kushal_Chawda · Accepted solution]

avg(total aggr(sum(Sales), [Service Date])) + 3*stdev(total aggr(sum(Sales), [Service Date])))

[satishkurra]

This worked. Thanks a ton @Kushal_Chawda