Dynamic Set based on Selections Made

[KJM]

Hi folks,

I'm creating a community sheet in a Qliksense saas app to track the sales of a weekly product of the week against its sales week last year (which may not be the same sales week this year).   I don't have access to the script, to create variables, or to create master items.   I'm trying to use the following expression to achieve a dynamic comparison week based on which EAN Codes (bar codes) are selected.  It should show the sales against the sales in its respective week last year.  Note, there could be multiple products in some weeks.

The code however returns null. I know that if I just use Sum([Sales Value Gross]) / Sum({$<Year={$(vLastYr)},Week={'39'}>} [Sales Value Gross]) the correct value comes through, so the issue is with my If statement.  I've tried using match instead and other variations.  Any help is gratefully received!

K.

If(
    [ePOS EAN] = '5449000091130' or
    [ePOS EAN] = '5449000009814' or
    [ePOS EAN] = '5449000133342' or
    [ePOS EAN] = '5449000304438',
    Sum([Sales Value Gross]) / Sum({$<Year={$(vLastYr)},Week={'39'}>} [Sales Value Gross]),
    If(
        [ePOS EAN] = '5012006050083',
        Sum([Sales Value Gross]) / Sum({$<Year={$(vLastYr)},Week={'40'}>} [Sales Value Gross]),
        If(
            [ePOS EAN] = '5720181303081' or
            [ePOS EAN] = '8720181303074' or
            [ePOS EAN] = '8720181303067',
            Sum([Sales Value Gross]) / Sum({$<Year={$(vLastYr)},Week={'41'}>} [Sales Value Gross])
        )
    )
)

If(
[ePOS EAN] = '5449000091130' or
[ePOS EAN] = '5449000009814' or
[ePOS EAN] = '5449000133342' or
[ePOS EAN] = '5449000304438',
Sum([Sales Value Gross]) / Sum({$<Year={$(vLastYr)},Week={'39'}>} [Sales Value Gross]),
If(
[ePOS EAN] = '5012006050083',
Sum([Sales Value Gross]) / Sum({$<Year={$(vLastYr)},Week={'40'}>} [Sales Value Gross]),
If(
[ePOS EAN] = '5720181303081' or
[ePOS EAN] = '8720181303074' or
[ePOS EAN] = '8720181303067',
Sum([Sales Value Gross]) / Sum({$<Year={$(vLastYr)},Week={'41'}>} [Sales Value Gross])
)
)
)

[vincent_ardiet_]

As you can have multiple EAN code selected, this cannot work, you would have to use an aggregation for [ePOS EAN] like "concat" and then use a wildmatch. But this is not ideal.
However, you should be able to write the expression without using IF. 
Like this:

RangeSum(
   ({<[ePOS EAN]={'5449000091130','5449000009814','5449000133342','5449000304438'}>} Sum([Sales Value Gross]) / Sum({<Year={$(vLastYr)},Week={'39'}>} [Sales Value Gross]))
  ,({<[ePOS EAN]={'5012006050083'}>} Sum([Sales Value Gross]) / Sum({<Year={$(vLastYr)},Week={'40'}>} [Sales Value Gross]))
  ,({<[ePOS EAN]={'5720181303081','8720181303074','8720181303067'}>} Sum([Sales Value Gross]) / Sum({<Year={$(vLastYr)},Week={'40'}>} [Sales Value Gross]))
)

[Gabbar]

The possible reason it returns null value is that the expression you are using is not a measure(It is measure w.r.t [ePOS EAN]) so try adding the same expression in the table and also add the dimension [ePOS EAN], If the data comes in the table but total is still null then use
:- Sum(aggr(Expression,[ePOS EAN]))
If it doesnt work or anything else is needed please provide a sample data .