Sign InHelp

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Discussion board where members can get started with Qlik Sense.

- Qlik Community
- :
- Qlik Sense
- :
- Qlik Sense Enterprise
- :
- Qlik Sense Enterprise Discussions
- :
- New to Qlik Sense
- :
- Simple math expression does not seem to be working...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Not applicable

03-19-2015
09:52 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

1,982 Views

1 Solution

Accepted Solutions

hic

Employee

03-26-2015
12:44 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

12 Replies

hic

Employee

03-19-2015
09:56 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

See Use Aggregation Functions!

HIC

10 Views

mov

Esteemed Contributor III

03-19-2015
09:56 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

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

10 Views

Not applicable

03-19-2015
12:41 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

10 Views

Not applicable

03-19-2015
01:19 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

10 Views

hic

Employee

03-19-2015
01:27 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

10 Views

Not applicable

03-19-2015
02:57 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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...

10 Views

hic

Employee

03-19-2015
03:14 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Can you post a sample file?

HIC

10 Views

Not applicable

03-19-2015
03:24 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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?

10 Views

hic

Employee

03-20-2015
04:31 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

10 Views