Qlik Community

New to Qlik Sense

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

Announcements
Don't miss the upcoming Q&A with Qlik session on Qlik Application Automation on November 16th! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jerry_ile
Contributor III
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 NameCoupon CodeCoupon valuecustomersOrdersStart DateEnd Date
40% OFF EVERYTHING (June)TAKE40£90,3463,1953,34924/06/201908/07/2019
50% off everything (1st July)TAKE50£58,1641,1431,17401/07/201908/07/2019
Sept 25% OFF EVERYTHINGSEPT25£41,6592,2832,43021/09/201802/11/2018
30% off non sale productsNON30£39,9842,1922,31330/11/201828/12/2018
30% off everything Cyber weekCW30£34,4622,4982,63527/11/201818/12/2018
NEW 10NEW10£31,9444,8774,99101/08/201718/07/2019

 

Labels (1)
6 Replies
Ezir
Creator II
Creator II

Hi @jerry_ile ,

Could you show the formulas to understand better?

Have you tried using the P() and E() functions? See https://community.qlik.com/t5/QlikView-App-Development/P-E-and-where-do-you-use-them/td-p/457847

jerry_ile
Contributor III
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 NameCoupon CodeCoupon valuecustomersOrdersStart DateEnd Date
40% OFF EVERYTHING (June)TAKE40£90,3463,1953,34924/06/201908/07/2019
50% off everything (1st July)TAKE50£58,1641,1431,17401/07/201908/07/2019
Sept 25% OFF EVERYTHINGSEPT25£41,6592,2832,43021/09/201802/11/2018
30% off non sale productsNON30£39,9842,1922,31330/11/201828/12/2018
30% off everything Cyber weekCW30£34,4622,4982,63527/11/201818/12/2018
NEW 10NEW10£31,9444,8774,99101/08/201718/07/2019
sunny_talwar

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?

jerry_ile
Contributor III
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

 

 

jerry_ile
Contributor III
Contributor III
Author

Any Ideas? @sunny_talwar 

sunny_talwar

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