Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
A script file with this line should create your variable:
SET e_varibale = num((COUNT([countfield]) / COUNT(TOTAL [countfield])), '#.##0,00 %');
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.
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,'@','$');
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.
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?
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.
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