Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
Not applicable

Re: Struggling with formulas in charts

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%')

6 Replies
Gysbert_Wassenaar
Not applicable

Re: Struggling with formulas in charts

Maybe with a bar chart like in the attached example.

comm67515.png


talk is cheap, supply exceeds demand
Or
Not applicable

Re: Struggling with formulas in charts

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

Re: Struggling with formulas in charts

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

Re: Struggling with formulas in charts

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

Re: Struggling with formulas in charts

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
Not applicable

Re: Struggling with formulas in charts

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%')