How to sum and divide variables in set analysis bar chart?

[#1]

Hello,

i am new in qlik sense. I need to do a bar chart with suppliers as dimension and as measure i need the sum of the cancelled and closed items divided the received items.

In loaded data i have an excel table with all the items with thei status (received, closed or cancelled) and the dates when they hae been received, closed or cancelled.

In order to know the number of the closed, cancelled and received items in the analysis period I set three variables:

eg.

=Count({<[Data Cancellazione]= {">=$(=vStartOfPreviousMonth)<=$(=vEndOfPreviousMonth)"}>}[Numero CR])

Now i am trying to create a new measure that is the sume of two variables (cancelled and closed items)/received items

((N_Cancellate)+(N_Chiuse))/(N_Ricevute)

When I insert this measure in the bar chart the value is the same for all the suppliers.

How can I get the right value for each supplier?

Thanks

Ila

[thomaslg_wq]

Hi Ila,

I have maybe an idea : do you know that there is a big difference in defining a variable with or without a first "=" sign ?

When, in your variable you write for your "eMyVariable" variable: "=sum(Sales)", the result will be always the same in the chart but if you write "sum(Sales)", the value will be well aggregated by the dimension in the chart.

The reason is the variable is simply a set of characters, and when you're calling a variable, these characters will replace it.

When in a chart you call $(eMyVariable), in the first example, the characters will be the result of the non-aggregated calculation (so if the total sum is 10000, the called characters are "10000" and every dimensiion value will show a 10000 value bar

Without the "=" sign as in the second example, the characters will be "sum(Sales)", so the calculation will be made in the chart, and then well-aggregated.

Also, please note a qlik sense issue: when you redefine a variable that contained a first "=" sign, it disappears and you have to write it again before saving the variable.

BR,

Thomas Le Gall

[#3]

Hello Thomas,

thanks for your suggestion. The issue was caused the "=" sign before the expression in the definiton of the variable. Now I corrected it in this way and it is working:

Count({<[Data Ricezione]= {">=$(=vStartOfPreviousMonth)<=$(=vEndOfPreviousMonth)"}>}[Numero CR])

and I also re-wrote the other expression in this way:

(($(canc_1))+($(chiuse_1)))/($(ricevute_1))

Now, I have  another question regarding an expression with multiple conditions. I have to calculate a number of items that are on time. The calculation should be count the number of CR that have the "Data avvio UAT pianificata" between the beginning of the month and the end of the month AND the Data avvio UAT effettiva less or egual than the Data avvio UAT pianificata AND different from null.

I wrote the espression below but it returns a wrong value. It seems it is not taking into account the 2° and 3° conditions.

count({<[Data avvio UAT pianificata]= {">=$(=vStartOfPreviousMonth)<=$(=vEndOfPreviousMonth)"},

[Data avvio UAT effettiva]= {"<=$([Data avvio UAT pianificata])"},

[Data avvio UAT effettiva]= {*}>}[Numero CR])

Do you have any idea why?

Thanks a lot,

Ila

[thomaslg_wq]

I would say you can remove the third condition. And also I dont understand

why you put the "=" sign in $(=vStart.

Try with only the first two. Also you can try to write in a text object

='YourExpression' and see what qlik understands and why it doesn't work

Thomas Le Gall

Le 9 juil. 2017 21:43, "ila ds" <qcwebmaster@qlikview.com> a écrit :

[jonathandienst]

The second condition for [Data avvio UAT effettiva] will overwrite the first. So the first condition will bbe ignored and the second will take all non-null values.

The first [Data avvio UAT effettiva] condition will also only consider nulls, so perhaps you just need to remove the second one.

[mangalsk]

if you add '=' then it will evaluate answer and same will be applied everywhere instead if you want to store expression in variable don't use = in front of expression so that it will evaluate individually for everyone