topic Re: Pick Function QlikSense in App Development

Pick Function QlikSense

satishkurra — Fri, 04 Oct 2024 00:53:37 GMT

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

Re: Pick Function QlikSense

Vegar — Fri, 04 Oct 2024 04:41:11 GMT

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'))] ))

Re: Pick Function QlikSense

satishkurra — Fri, 04 Oct 2024 11:39:52 GMT

Hi @Vegar

Tried both options and no luck

Re: Pick Function QlikSense

Kushal_Chawda — Fri, 04 Oct 2024 12:16:08 GMT

@satishkurra try like below

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

Re: Pick Function QlikSense

satishkurra — Fri, 04 Oct 2024 13:20:15 GMT

Hi @Kushal_Chawda , Tried and no luck

Re: Pick Function QlikSense

Kushal_Chawda — Fri, 04 Oct 2024 13:24:09 GMT

@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.

Re: Pick Function QlikSense

satishkurra — Fri, 04 Oct 2024 14:03:13 GMT

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

Re: Pick Function QlikSense

Kushal_Chawda — Fri, 04 Oct 2024 14:32:20 GMT

@satishkurra try below in reference line

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

Re: Pick Function QlikSense

Kushal_Chawda — Fri, 04 Oct 2024 14:36:37 GMT

@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])))

Re: Pick Function QlikSense

satishkurra — Fri, 04 Oct 2024 15:02:15 GMT

This worked. Thanks a ton @Kushal_Chawda