topic Re: Very simple calculation - why can't I make it into an e_variable? in QlikView

Very simple calculation - why can't I make it into an e_variable?

datanibbler — Thu, 06 Aug 2015 09:01:36 GMT

Hi,

one very simple expression that I have found to be extremely useful in analyzing trends and similarities among my data and that I add to nearly all my listboxes these days is

>> num((COUNT([countfield]) / COUNT(TOTAL [countfield])), '#.##0,00 %') <<

=> That is quite simply the relative percentage of my records that have a specific value in that field.

Just now I'm trying to make that into an e_variable to store in a qvs file so that I can easily add it in all my apps. however, there must be something tricky.

I first tried to make a local variable of the countfield which I certainly need to set as a parameter to this expression. I did that in the variable_manager at first, without quotes or anything.

However, when I try to put that into the expression, I get completely different - and impossible - values (like 300%).

Now I realized that maybe I do need to define the variable with quotes, but that doesn't work either.

Obviously I am doing something wrong.

My objective is to write an expression in a qvs file to INCLUDE that will combine with another variable that must exist locally to denote the field to count. I know that works in principle.

Can anybody help me there?

Thanks a lot!

Best regards,

DataNibbler

Re: Very simple calculation - why can't I make it into an e_variable?

Gysbert_Wassenaar — Thu, 06 Aug 2015 09:17:20 GMT

A script file with this line should create your variable:

SET e_varibale = num((COUNT([countfield]) / COUNT(TOTAL [countfield])), '#.##0,00 %');

Re: Very simple calculation - why can't I make it into an e_variable?

datanibbler — Thu, 06 Aug 2015 09:39:15 GMT

Hi Gysbert,

no, I still cannot do it.

I can go like >> SET v_countfield = 'FKolli_ID'; << in the script, that generates me a variable that i can display without quotes or anything on the GUI - with the $() it doesn't work, though - with quotes (like >> '$(v_countfield)' << it works, but then it's just the same result-wise - I get completely impossible values.

Defining both that variable and the expression in the script like

>> LET e_percentage_countfield = num((COUNT('$(v_countfield)') / COUNT(TOTAL '$(v_countfield)')), '#.##0,00 %'); <<

does not seem to work.

Re: Very simple calculation - why can't I make it into an e_variable?

Gysbert_Wassenaar — Thu, 06 Aug 2015 09:46:16 GMT

Well, no. Because dollar expansion also happens in the script. Try

SET e_percentage_countfield = num((COUNT('@(v_countfield)') / COUNT(TOTAL '@(v_countfield)')), '#.##0,00 %');

LET e_percentage_countfield = replace(e_percentage_countfield,'@','$');

Re: Very simple calculation - why can't I make it into an e_variable?

datanibbler — Thu, 06 Aug 2015 09:58:33 GMT

Hi Gysbert,

sounds like a good idea. I'll try tomorrow - for the rest of today I have something different to do.

Right now, QlikView is reporting an error with a ',' which is a bit strange because I (should) have only distinct values there, no comma_values.

Well, I'll see about that tomorrow.

Thanks and have a nice day!

I'll be back here if I need any more help.

Re: Very simple calculation - why can't I make it into an e_variable?

datanibbler — Thu, 06 Aug 2015 13:16:15 GMT

Hi,

I have implemented the formula like you proposed it now. It seems to work - but when I put >> = $(e_percentage) <<

in the listbox, I get for the first value there a result of 2.500,00 % while the true value should be about 0.01% (with no selection, the count is 25, ringring) - so there is something wrong with my separator signs - but what?

My thousand_separator in the script is the '.', the decimal_separator is the ',', and that's just how I have made up the num() function in that expression.

So do I have to wrap that into a num#() function to turn those around so that they match QlikView's internal calculating_engine?

Re: Very simple calculation - why can't I make it into an e_variable?

datanibbler — Thu, 06 Aug 2015 14:17:45 GMT

Okay,

now I got it. Well, it was quite easy to locate the error given the count of the first value is 250 and I got something like 2.500,00 % - so the error was in the definition of my variable v_countfield.

Now I have the definition of that with no quotes, like

>> SET v_countfield = FKolli_ID <<

in the script (FKolli_ID is the field I count) and now it works.

Re: Very simple calculation - why can't I make it into an e_variable?

datanibbler — Fri, 07 Aug 2015 07:45:36 GMT

Hi,

one more question. This works fine so far - I have used the TOTAL option in that expression, but the expression is not independent of user selections.

That is important in case there is a subset of the data that I need to use selections to get - and I want to analyze it further. That wouldn't work if user_selections were generally being ignored.

<=> But the way it is now, as soon as I select one value in a listbox, the percentage jumps to 100%. Of course.

=> I would like to avoid this by making the expression independent of selections in that specific listbox (the one the expression is being used in), but to react to user_selections elsewhere.

Can that be done without again breaking the point of using the expression in a qvs file?

Thanks a lot!

Best regards,

DataNibbler