Help with aggr formula

[jerry_ile]

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

[Ezirraffner]

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]

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

[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]

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]

Any Ideas? @sunny_talwar 

[sunny_talwar]

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