Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Simple math expression does not seem to be working in Qlik Sense

Now I admit, I don't have much experience in writing expressions, however based on what I have read in the forums I believe I have this set up correctly.  My import contains the price per item and the qty for the price (3/$1.00) and also Qty sold but does not have the total amount sold.  My expression is ([Cur Price]/[Cur Price Qty])*[Qty Sold] and I have used the "create new measure" tool in Quick Sense to insert the fields from my database. When I try to use it in a graph, I use my Store field as a Dimension and the created measure.  Qlik Sense then tells me "The chart is not displayed because it contains only undefined values.  When I load the Store as a measure and only Qty Sold it graphs correctly.

I am also having trouble with a markdown expression, ([Cur Price]/[Cur Price Qty])-([Reg Retail]/[Reg Retail Unit]) it is giving me the same error.

These are the first measures I have tried to create, so I am clearly doing something wrong.  Anyone able to tell me what?

1 Solution

Accepted Solutions
hic
Former Employee
Former Employee

I have looked at it and cannot find any problem, but you need to tell me what your expectations are...

If I use

   Sum(([Reg Retail]/[Reg Retail Unit])*([Qty Sold]))

as expression for Markdown, I get the same result in Qlik Sense as in Excel.

HIC

Image2.png

Image4.png

View solution in original post

12 Replies
hic
Former Employee
Former Employee

It seems as if you're not using any aggregation function, like Sum(...).

See Use Aggregation Functions!

HIC

Anonymous
Not applicable
Author

Try to add aggregation, I guess sum() in your case:

sum(([Cur Price]/[Cur Price Qty])*[Qty Sold])

Not applicable
Author

But what if I want to know this for each item?  For example:

UPC 4013022359 sells for 3/1.00 on sale but 2/1.00 regular price and there were 122 of them sold.  The first calculation should show me ..33 * 122 = 40.26 for the one item, I don't need to sum anything.  I would use the sum calculations in the graph to get totals of all products and so on.
By the same token, my markdown should show me .50-.33=.17*122=20.74 for the one item.  The expression contains all the math I need in it.  I guess I am confused on the aggregation.  I also don't see anything in the aggregation article referencing taking the product of an expression.

Not applicable
Author

The sum aggregation you suggested worked to make it graph, but the numbers aren't right.  For example, Item 1530043006 had a regular price of 4/5.00 (1.25 unit) and a sale price of 10/10.00 (1.00 unit price).  The markdown expression is giving me a "0"  According to the data I am importing, I sold 1 item at 1.00 so my markdown should be .25 on that item.  Most items using the markdown calculation are showing 0 but a few items are showing a total, for example, item 7056083745 had a regular price of 1/2.19 and a sale price of 2/3.00 (markdown of .69) and we sold 3 of those.  My markdown formula is returning a value of 1.38 which when divided by the 3 units sold gives us a markdown of .46  Somewhere the math is messing up.

hic
Former Employee
Former Employee

It's impossible to say what's right or wrong without seeing the data model. Michael's formula is the right one if all fields are in the same table.

The logic in this formula is:

  1. Generate all combinations of the values of [Cur Price], [Cur Price Qty] and [Qty Sold]
  2. Calculate the value of each record (the formula inside the Sum( ... ))
  3. Sum the result of 2

If this is the wrong algorithm for what you want to calculate, you may have to try some other combination, like

sum([Cur Price]/[Cur Price Qty])  *  Sum([Qty Sold])


HIC

Not applicable
Author

First of all, thank you very much for the help. 


Current price = 10/10.00 (1.00 unity price) and 34 of this item has been sold. 

I have tried  the following:
sum([Cur Price]/[Cur Price Qty])  *  Sum([Qty Sold])  gave me 136.00

([Cur Price]/[Cur Price Qty])  *  Sum([Qty Sold]) gave me 68.00

sum([Cur Price]/[Cur Price Qty])  *  ([Qty Sold]) gave me 68.00

I am still digging through some other options to try...

hic
Former Employee
Former Employee

Can you post a sample file?

HIC

Not applicable
Author

Sure, I don't see any export option on Qlik Sense and my desktop icon paths to the AppData folder but does not actually have the installation there.  Do you know what file I should grab or how to export it?  In the mean time here are screenshots of the data model and the dashboard I am putting together.  As you can see, it's a pretty simple project if I can figure out the mathematical part.  You will notice the item Plum Red Import shows up mutliple times, this is reading from a MySql database containing movement data for 10 stores, so an item could show up anywhere from 0-10 times and I am trying to be able to show the total $ amount sold as well as the amount the item was marked down, but that data is not part of my movement file so I have to create it through the expressions.Capture.JPGCapture2.JPG

Could it possibly have anything to do with the qty value being an integer and the price being a decimal in the database?

hic
Former Employee
Former Employee

You should have a qvf-file in C:\Users\<User>\Documents\Qlik\Sense\Apps.

The fact that one number is an integer and another is a decimal number should not matter - as long as the decimal number is interpreted as a number. Check that it is! (It should be right-aligned, and it should be possible to create a Sum() from it.)

HIC