Issue with conditional previous year expression

[lindbergkarlsta]

Hi

Hope you can assist me with a little issue. This last year formula works fine:

sum({<Year = {$(=max(Year) - 1)}>} SalesValue)

But when creating a dynamic expression:

if($(vKPISales) = 'Sales value',  sum({<Year = {$(=max(Year) - 1)}>} SalesValue),

if($(vKPISales) = 'Sales volume', sum({<Year = {$(=max(Year) - 1)}>} SalesVolume),’N/A’))

The straight table total row gives correct value. But rows that have sales in previous year (max(Year)-1) but not in current year (max(Year)) returns ’N/A’ with the expression above. Does anyone have an idea of why, and what to do about it?

Thanx /Johan

[jaibau1993]

Quite weird! Could you share a sample of your QlikView so we can test your issue? Aparently the sintax of your expression is correct.

Jaime.

[marcus_sommer]

Your variable contains string-values and therefore you need to wrap the call of it with single-quotes like:

if('$(vKPISales)' = 'Sales value',  sum({<Year = {$(=max(Year) - 1)}>} SalesValue),

if('$(vKPISales) '= 'Sales volume', sum({<Year = {$(=max(Year) - 1)}>} SalesVolume),’N/A’))

Personally I would try to avoid the if and simplifying it with something like this:

sum({<Year = {$(=max(Year) - 1)}>} [$(vKPISales)] )

Of course you need to ensure that your variable-values are exactly the same like your fieldnames.

- Marcus

[jaibau1993]

Of course, how could I not see that? It solves Johan issue for sure!

[lindbergkarlsta]

Hi and thanx for response

Unfortunately it didn't solve the issue. So, I've attached an example app.  

The requirement is to present a table with Dimensions + a KPI field containing Value, Volume, Margin %... (with different number formattings) in the rows, and Actuals, Actuals last year, Budget, Budget last year.... in the columns.

If you select "Sales value" and 2019 in example app it gives correct 80 in total row for Last year, but shows N/A for Dim1 'B'. Instead of N/A I would expect 30. 

BR/Johan

[marcus_sommer]

The cause is your selection of a year which is a regular field within your datamodel. There are of course possibilities within set analysis to bypass it but they could have also disadvantages - so it will always depend of all requirements which approach might be the most suitable. A common way to avoid some of these difficulties is to use for it also an extra field within an island table - similar like your measure field.

But like above mentioned I would do it a bit different by extending the measure table to the real fieldnames and also including the format. This means somethin like this one:

- Marcus

[lindbergkarlsta]

Thanx for your input Marcus, much appreciated. 

One core requirement is the ability to show Value, Volume, Margin % in the same column at the same time. (My example app is just a slice of the actual app containing lots of measures which forms a P&L-like structure for Sales). It seems that your version only works for one measure at a time, with "Always one selected value".

As you wrote, it is my Year-filter that causes the issue. But I don't see why it works perfectly for current year and last year's total, but not for rows Last year if there is no data in current year. 

Storing the real measure name and format in data island table instead of in variable is a good point.

BR/Johan

[marcus_sommer]

Yes, your conclusion that my suggestion worked only with a single measure is correct. Because of the fact that Qlik doesn't provide an evaluate() function within the UI which could return valid fieldnames and/or expressions there is no way to use multiple different expressions within a measure unless they query the fieldvalues within conditions and branching then to the appropriate expression - quite similar to your nested if-loops.

Better than using the if-loops would be a pick(match()) like:

pick(match(Measure, 'A', 'B', 'C'),
    sum(Field), avg(Field), sum(Field2))

whereby the measure- and the expression-list could in general be maintained and loaded from a table and called here with a concat() in a $-sign like:

pick(match(Measure, '$(=concat(MeasureList, chr(39) & ',' & chr(39)))'),
    sum(Field), avg(Field), sum(Field2))

This is just an object-structure recommendation and independent from your selection-issue which needs to be handled through further set analysis in the expressions and/or adjustments within the datamodel (like the above mentioned island-tables, different table-structures, population from missing data, ...).

- Marcus

[lindbergkarlsta]

Even if we didn't solve my core question you have confirmed there is no quick fix the way the logic is designed today. I will take the discussion with my customer what way to take from here. 

Thanx again.

BR/Johan