topic Re: Pick function not working in App Development

Pick function not working

MynhardtBurger — Thu, 15 Aug 2019 10:58:21 GMT

This question is in relation to the following example qlik sense model: https://www.dropbox.com/s/vvm1m6jlm77ues1/YTD%20incomplete%20timeline.qvf?dl=0

I have an expression which calculates a current month or YTD value based on a field in a data island.

If I make a selection on the [Financial Period] field and there is no data rows for item A for that specific financial period, then the YTD total isn't calculated on the item A row. The Grand Total however still calculates correctly.

Why is that?

The expression:

Pick( [Sort], Sum({<[Financial Period]={'$(vCurrentFinancialPeriod)'}>} Sales), Sum({<[Financial Period]={"<=$(vCurrentFinancialPeriod)"}>} Sales) )

No selectionsSelection where item A doesn't have data in the specific financial period. Note that A shows as null in the YTD column calc, while the grand total is correct.

Re: Pick function not working

marcus_sommer — Thu, 15 Aug 2019 15:26:24 GMT

The comparison against your variable is different in your expression - the first compares on a string-level and the second is a numeric one. Further it's not clear if your date-fields contain really a date or maybe a timestamp. Both things may lead to unexpected results if you are unaware about it.

Beside this I suggest to use a real calendar with a single KEY field to the fact-table (and resolving the synthetic key by doing this). Further if it's an essential requirement to show KPI's like YTD, MTD, CM and so on and might be useful to flag them within calendar and/or to use a separat calendar to select the inital period for the calculations and/or to apply an as-of-table for it. I think the following will be quite useful for your task: How-to-use-Master-Calendar-and-Date-Values

- Marcus

Re: Pick function not working

MynhardtBurger — Sat, 17 Aug 2019 10:02:16 GMT

Hi Macrus_sommer

I tried to implement your suggestions and attached the updated model. I am however still experiencing the same problem.

Both applying the set analysis on a numeric field or a date field (with the correctly formatted string) still exhibits the same error.

PS. YTD and CM are KPIs which we normally use, but using a flag inside the data load isn't appropriate because these needs to be calculated relative the the user's selection. That is why I use set analysis to search for {"<=$(vMaxMonth)"} or {'$(vMaxMonth)'} to calculate YTD and CM respectively.

Re: Pick function not working

marcus_sommer — Mon, 19 Aug 2019 07:38:03 GMT

Unfortunately I don't have Sense available and couldn't look into your application. Quite probably there is just a small syntax- and/or data-issue and with a bit systematically testing it shouldn't be too difficult to find the cause.

If a set analysis with variables/expressions don't work replace them with fixed values to check if the expression itself worked - you need to be sure before you tries to find and fix issues within the set analysis. If your manually values work and the expression returned the expected value you could apply the variables/expressions again - maybe not so whole one else so detailed as possible and then step by step adding them together ...

Quite useful is also to apply the variables/expressions from the set analysis within another object (table, kpi, ...) to see their results there and maybe also wrapping them with num(), isnum() and similar checking if the values behind the visual display have the right values.

- Marcus

Re: Pick function not working

MynhardtBurger — Wed, 28 Aug 2019 11:10:06 GMT

Hi Marcus,

I've narrowed the problem down to how the [Sort] value is being calculated based on the row/column in the pivot table. If I replace [Sort] in my pick function with a hardcoded value (eg. 2), then it calculates properly.

So when there is no [Item] for a selected [Year Month Date] period, then [Sort] returns NULL, breaking the Pick() expression. This is evaluated for each cell in the pivot table independently. See my 3rd screenshot as well.

How do I ensure that [Sort] returns a value relating to the pivot table column [Heading] field, whether the is data in the [Item] row dimension in the user's selection or not?

I've tried using combinations of the following with no success:

ValueList() as a synthetic dimension
Using Match() in the pick function
ValueList() as a syntehetic dimension combined with Match(ValueList()) in the pick function. This causes other weird effects as well.

I'd like to avoid padding my raw data with 0 rows for all combinations of [Item] and [Year Month Date].

Thank you

Here is my expression:

Pick( [Sort], Sum({1<[Year Month Date]={'$(vMaxYearMonthDate)'}>} Sales), Sum({1<[Year Month Date]={"<=$(vMaxYearMonthDate)"}>} Sales) )

vMaxYearMonthDate: =Date(Max([Year Month Date]),'YYYY-MMM')

Re: Pick function not working

sunny_talwar — Wed, 28 Aug 2019 11:37:44 GMT

Can you try this

Pick( Only({1} TOTAL <[Sort]> [Sort]), Sum({1<[Year Month Date]={'$(vMaxYearMonthDate)'}>} Sales), Sum({1<[Year Month Date]={"<=$(vMaxYearMonthDate)"}>} Sales) )

Re: Pick function not working

MynhardtBurger — Wed, 28 Aug 2019 11:55:28 GMT

Hi Sunny_talwar,

That doesn't work. Only({1} TOTAL <[Sort]> [Sort]) doesn't return anything.

I've attached the latest version of the example for reference.

Re: Pick function not working

marcus_sommer — Wed, 28 Aug 2019 12:02:10 GMT

Generating missing values within the script is not unusual to avoid the various trouble with NULL. Of course a whole cartesian product of zero-values could become quite expensive but often you need to do it only for certain sub-set of your data and could be therefore more suitable as to handle the NULL anywhere in the UI.

From the effect a bit similar is not to use a synthetic UI listing with value-list/loop else to create them as native table in the script. This could be done with something like:

t: load recno() as ValueListDimension autogenerate 5;

- Marcus

Re: Pick function not working

sunny_talwar — Wed, 28 Aug 2019 12:40:04 GMT

Can you try this

Pick( Only({1} TOTAL <Heading> [Sort]), Sum({1<[Year Month Date]={'$(vMaxYearMonthDate)'}>} Sales), Sum({1<[Year Month Date]={"<=$(vMaxYearMonthDate)"}>} Sales) )

Re: Pick function not working

MynhardtBurger — Thu, 29 Aug 2019 07:58:12 GMT

Thanks Sunny_talwar.

After your initial suggestion I also looked into how TOTAL works with the <Field list> qualifier and happened upon the same solution you showed. Defining the aggregation scope.

I ended up using the following, as I wanted the user to still be able to select only "Current Month" or "Year to date" on the headings:

Pick( Only(TOTAL <[Heading]> [Sort]), Sum({1<[Year Month Date]={'$(vMaxYearMonthDate)'}>} Sales), Sum({1<[Year Month Date]={"<=$(vMaxYearMonthDate)"}>} Sales) )

Thanks to Marcus as well for your help, especially with the calendar links.

In my main application where this was the problem, creating missing values to avoid NULL ballooned the data set from 2m rows to 50+m and impacted the performance. I am glad I can avoid that now.