Qlikview Expression Error - Unable To Determine The Error

[pnn44794]

Hello:

I've been trying (and probably for too long) to determine what's wrong with the following expression (Expression Editor says "Errors in expression"):

[Q1 Consumed] is another expression calculated in the straight table prior to this expression.

Any help will be appreciated.

[Donicc]

Hi,

Try using dollar expansion on vQtrNum. E.g., $(vQtrNum).

I'm not sure about  the problem you are trying to solve or the layout of your data model so I'll try to give some general pointers.

Verbose code is difficult to troubleshoot. First, try to simplify the formula for clarity.

vMonth = num(month(now())) ;
vQtrNum = If(vMonth > 0 and vMonth <= 5, 1,
   If(vMonth > 5 and vMonth <= 8, 2,
    If(vMonth > 8 and vMonth <= 11, 3
     If(vMonth = 12, 4)
    )
   )
  );
   
if( vQtrNum = 1 and match(Forecast_Product_Base,
 'VSI Silver Base',
 'VSI Gold Base',
 'VSI Platinum Base',
 'VSI Power Base',
 'PSI Base',
 'PSI HDD Base',
 'GP Block (GB)',
 'IaaS Block (GB)',
 'File (GB)',
 'PSI Custom'),
 [Q1 Consumed] / vQtrNum);

Once you get that working, you can look at ways to simplify further using more traditional Qlikview methods. Such as with set analysis:

sum ({<Forecast_Product_Base={ 'VSI Silver Base', 'VSI Gold Base', 'VSI Platinum Base', 'VSI Power Base', 'PSI Base', 'PSI HDD Base', 'GP Block (GB)', 'IaaS Block (GB)', 'File (GB)', 'PSI Custom'}>} [Q1 Consumed]) / vQtrNum

If you have a lot of products that are typically grouped for these types of calculations, you can define those groups during the load for handy reference in formulas.

if(match(Forecast_Product_Base,
 'VSI Silver Base',
 'VSI Gold Base',
 'VSI Platinum Base',
 'VSI Power Base'), 'VSIProductGroup',
 if(match(Forecast_Product_Base,
  'PSI Base',
  'PSI HDD Base',
  'PSI Custom'), 'PSIProductGroup', 'No Product Group Defined')
)   as ProductGroup

Then your set analysis would look something like:

sum ({<ProductGroup={ 'VSIProductGroup', 'PSIProductGroup'>} [Q1 Consumed]) / vQtrNum

[pnn44794]

Hello Donicc:

Thank you for your reply.  Great suggestions and ideas.  For the Load script, I changed the vQtrNum definition pretty much as suggested like the following.  It definitely simplifies it:

I then tried your next suggestion and I still get "Errors in expression" in the expression editor.  I did try the $ expansion as well.  Interestingly, I have the same problem with the following expression, but it actually works:

I will try your other suggestions.  I'm thinking I will mark your response as correct, but will wait to see if you have another response.

To give you a little more background, I always have the same products, so probably having one product group would work.  From there, using your suggestions, I  could then work on simplifying the expression.  For this particular expression, I trying to get quarterly order averages for each product on a monthly basis.  That's why I have x.333 numbers for when we're not at the end of a quarter (it's not 100%, but close enough for this exercise).

Thanks again for your response and help.

[Donicc]

Try wrapping the field in an aggregation :

sum([Q1 Consumed]) /  vQtrNum

(sum([Q1 Consumed]) +Sum( [Q2 Consumed]) )) / vQtrNum

...

Another thing you can try is to calculate QtrNum at load time, making the value available as a field. Then your formula for Qtr 2 with set analysis would be something like:

sum( {<QtrNum={'1','2'}>}   [Consumed] )  /  vQtrNum

[pnn44794]

No luck.  I still have the "Errors in expression" error in the editor, plus the values blank out when I try your expression.  Please note, and I should have mentioned this before, [Q1 Consumed] thru [Q4 Consumed] are values previously calculated in the straight table.  Not sure if that matters.

In any event, the calculation I have below seems to be working, even with the editor still saying "Errors in expression".  However, I will mark your original answer as correct.  You gave me some excellent suggestions and I have successfully implemented one of them.  Thank you so much for your help.