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?
Solved! Go to Solution.
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.
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.
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.
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:
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])
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...
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.
Could it possibly have anything to do with the qty value being an integer and the price being a decimal in the database?
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.)