Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
pnn44794
Partner
Partner

Qlikview Expression Error - Unable To Determine The Error

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.

Labels (2)
4 Replies
Donicc
Contributor II
Contributor II

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
Partner
Partner
Author

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
Contributor II
Contributor II

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
Partner
Partner
Author

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.