
Re: Simple math expression does not seem to be working in Qlik Sense
Henric Cronström Mar 19, 2015 9:56 AM (in response to Aaron Martin)It seems as if you're not using any aggregation function, like Sum(...).
See Use Aggregation Functions!
HIC

Re: Simple math expression does not seem to be working in Qlik Sense
Aaron Martin Mar 19, 2015 12:41 PM (in response to Henric Cronström )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.


Re: Simple math expression does not seem to be working in Qlik Sense
Michael Solomovich Mar 19, 2015 9:56 AM (in response to Aaron Martin)Try to add aggregation, I guess sum() in your case:
sum(([Cur Price]/[Cur Price Qty])*[Qty Sold])

Re: Simple math expression does not seem to be working in Qlik Sense
Aaron Martin Mar 19, 2015 1:19 PM (in response to Michael Solomovich )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.

Re: Simple math expression does not seem to be working in Qlik Sense
Henric Cronström Mar 19, 2015 1:27 PM (in response to Aaron Martin)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:
 Generate all combinations of the values of [Cur Price], [Cur Price Qty] and [Qty Sold]
 Calculate the value of each record (the formula inside the Sum( ... ))
 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

Re: Simple math expression does not seem to be working in Qlik Sense
Aaron Martin Mar 19, 2015 2:57 PM (in response to Henric Cronström )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...

Re: Simple math expression does not seem to be working in Qlik Sense
Henric Cronström Mar 19, 2015 3:14 PM (in response to Aaron Martin)Can you post a sample file?
HIC

Re: Simple math expression does not seem to be working in Qlik Sense
Aaron Martin Mar 19, 2015 4:56 PM (in response to Henric Cronström )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 010 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?

Re: Simple math expression does not seem to be working in Qlik Sense
Henric Cronström Mar 20, 2015 4:31 AM (in response to Aaron Martin)You should have a qvffile 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 rightaligned, and it should be possible to create a Sum() from it.)
HIC

Re: Simple math expression does not seem to be working in Qlik Sense
Aaron Martin Mar 20, 2015 7:07 AM (in response to Henric Cronström )OK attached is the QVF file.

Markdown Report.qvf 192.0 K


Re: Simple math expression does not seem to be working in Qlik Sense
Aaron Martin Mar 27, 2015 8:09 AM (in response to Henric Cronström )It appears you have an extra set of parenthesis around Qty Sold that I never tried because it stood alone and but that appears to have corrected the calculation! I applied this same principle to other calculations I was having difficulty with and it fixed those as well! Thank you so much for your help. I would have overlooked that entirely had I not seen you do it in your expression.







