# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for
Did you mean:
Contributor III

## Help with aggr formula

Hi All,

I need to compare coupon sales with the total sales across dates to find out what percentage of customers used a coupon to purchase

I have a table grouped by a Coupon Name, with summed sales for all transaction's using that coupon. These sales are across multiple intermittent dates.

An example table is below:

I've tried various aggr and total combinations but some Coupons such as New10 have ran intermittently across random dates so I am struggling to find a formula that will match dates the Coupon was used and total orders correctly if that date had a coupon sale

Hope this makes sense, would appreciate any help, I'm using Qlik Sense.

 Coupon Name Coupon Code Coupon value customers Orders Start Date End Date 40% OFF EVERYTHING (June) TAKE40 £90,346 3,195 3,349 24/06/2019 08/07/2019 50% off everything (1st July) TAKE50 £58,164 1,143 1,174 01/07/2019 08/07/2019 Sept 25% OFF EVERYTHING SEPT25 £41,659 2,283 2,430 21/09/2018 02/11/2018 30% off non sale products NON30 £39,984 2,192 2,313 30/11/2018 28/12/2018 30% off everything Cyber week CW30 £34,462 2,498 2,635 27/11/2018 18/12/2018 NEW 10 NEW10 £31,944 4,877 4,991 01/08/2017 18/07/2019

Labels (1)
• ### functions

6 Replies
Creator II

Hi @jerry_ile ,

Could you show the formulas to understand better?

Have you tried using the P() and E() functions?

Contributor III
Author

hi Ezir,

I have tried as suggested but having no joy

count({\$<[Order ID] = E({1<[Coupon Code] -={*}>})>} distinct [Order ID])

also tried

count({<[Coupon Code] = E([Coupon Code])>} distinct [Order ID])

I attempted to count the orders that had a null coupon code, i would then like to use that to get a percentage of orders made using the coupon compared to customers that didn't....didn't work

I also removed Coupon Name from the table but same result....any ideas?

 Coupon Name Coupon Code Coupon value customers Orders Start Date End Date 40% OFF EVERYTHING (June) TAKE40 £90,346 3,195 3,349 24/06/2019 08/07/2019 50% off everything (1st July) TAKE50 £58,164 1,143 1,174 01/07/2019 08/07/2019 Sept 25% OFF EVERYTHING SEPT25 £41,659 2,283 2,430 21/09/2018 02/11/2018 30% off non sale products NON30 £39,984 2,192 2,313 30/11/2018 28/12/2018 30% off everything Cyber week CW30 £34,462 2,498 2,635 27/11/2018 18/12/2018 NEW 10 NEW10 £31,944 4,877 4,991 01/08/2017 18/07/2019
MVP

All the rows seems to have coupons, what is the different between coupon sales vs total sales? I am not sure I understand what the issue is?

Contributor III
Author

Coupon Value is the amount discounted from the sales

e.g. sales (with a coupon use) were £1000, coupon was 20% Coupon value would be £200

Total sales regardless of whether a coupon was used or not is actually £2000 meaning 50% of customers used the coupon

I need to be able to show this % use  but don't seem to be able to exclude the coupon name or code to get to £2000 figure

Contributor III
Author

Any Ideas? @sunny_talwar

MVP

To tell you the truth... I don't really fully understand the issue at hand....

Tags
Community Browser