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

Struggling with formulas in charts

Hello,

This is a great community! Thanks everyone.

I have a sales table like this -

ProductQtyRRP (total)Sale Price (total)
Product A2200160
Product B15045
Product C2150120
Product A110070

I am trying to create a chart to find the best discount for products.

I think the best way is to show it this way -

x- axis - Discount

y- axis - products sold at this discount.

I might need to use ranges (0-10%, 10%-20%, etc ) as discounts are usually 5-10-15-20-25. I am not sure at this point how to do it.

Could someone give me an idea what dimensions, expressions I should use for line chart, or if it should be line chart at all? Or maybe there is a better way to use QV to find the best discounts using the data I have?

Thanks.

1 Solution

Accepted Solutions
whiteline
Master II
Master II

Hi.

You could use round()/floor() or class() function in dimension to have 'baskets' instead of separate values:

=floor([Discount percent], 0.01)

or

=aggr(class([Discount percent], 'x', 0.01), _put here dimension that represens the selling_)

The first one can also be used in script.

In addition you could change the text format with num() to have percents:

=num(floor([Discount percent], 0.01), '0%')

View solution in original post

6 Replies
Gysbert_Wassenaar

Maybe with a bar chart like in the attached example.

comm67515.png


talk is cheap, supply exceeds demand
Or
MVP
MVP

I would suggest you calculate the discount at the script level, and then use that field as your dimension. Your expression would be sum(Qty)

Not applicable
Author

Thanks Gysbert, but I don't think it's how I wanted to analyse my data.

I have the same product (RRP - 100$) sold with different prices -

Like this -

1 units for 100$

2 units for 90$ each

3 units for 80$ each

11 units for 80$ each

9 units for 80$ each

35 units for 70$ each.

This is a table I have.

Now I need to

1) Group the table above by sale price

1 units for 100$

5 units for 90$ each

20 units for 80$ each

35 units for 70$ each.

2) Show a bar chart similar to what you made but with Discounts (0%, 10%, 20%, 30%) instead of products.

Not applicable
Author

thanks orsh, I tried that, but it shows some discounts of 12000%. I most likely made a mistake in discount formula. Trying to fix now.

Not applicable
Author

Hi orsh,

Thanks!!!

Your approach works in general, but I have 2 problems -

1) (easy) my discounts are not shown as percents - it's a dimension on the graph as you recommended.

2) as you can see on the picture 10% is not exactly 10% it's sometimes 10.000001%, sometimes 9.9999%, so I need to somehow show them as the same thing.

graph.jpg

whiteline
Master II
Master II

Hi.

You could use round()/floor() or class() function in dimension to have 'baskets' instead of separate values:

=floor([Discount percent], 0.01)

or

=aggr(class([Discount percent], 'x', 0.01), _put here dimension that represens the selling_)

The first one can also be used in script.

In addition you could change the text format with num() to have percents:

=num(floor([Discount percent], 0.01), '0%')