Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am developing an App that has one table that has numerous dates, customers and a loyalty component.
| customer_id | dates | loyalty |
| loyalty 2 | Nov 20, 2023 | coupon |
| loyalty 3 | Dec 8, 2023 | coupon |
| loyalty 5 | Dec 12, 2023 | coupon |
| loyalty 9 | Dec 15, 2023 | coupon |
and a second table that has dates, customers and sales
| customer_id | dates | purchase |
| loyalty 1 | Nov 23, 2023 | $14 |
| loyalty 1 | Dec 6, 2023 | $12 |
| loyalty 2 | Nov 20, 2023 | $12 |
| loyalty 2 | Dec 11, 2023 | $15 |
| loyalty 3 | Dec 9, 2023 | $5 |
| loyalty 4 | Nov 30, 2023 | $14 |
| loyalty 4 | Dec 15, 2023 | $26 |
| loyalty 5 | Nov 22, 2023 | $16 |
| loyalty 6 | Dec 9, 2023 | $22 |
| loyalty 7 | Dec 4, 2023 | $17 |
| loyalty 8 | Nov 19, 2023 | $15 |
| loyalty 9 | Nov 23, 2023 | $22 |
once the development is done on the back end, I end up with a table with all dates, customers, loyalty and sales. I also set a source to identify the data.
| customer_id | dates | all data | Source |
| loyalty 1 | Nov 23, 2023 | $14 | sales |
| loyalty 1 | Dec 6, 2023 | $12 | sales |
| loyalty 2 | Dec 11, 2023 | $15 | sales |
| loyalty 2 | Nov 20, 2023 | $12 | sales |
| loyalty 2 | Nov 20, 2023 | coupon | sales |
| loyalty 3 | Dec 9, 2023 | $5 | sales |
| loyalty 3 | Dec 11, 2023 | coupon | sales |
| loyalty 4 | Nov 30, 2023 | $14 | sales |
| loyalty 4 | Dec 15, 2023 | $26 | sales |
| loyalty 5 | Nov 22, 2023 | $16 | sales |
| loyalty 5 | Dec 12, 2023 | coupon | sales |
| loyalty 6 | Dec 9, 2023 | $22 | sales |
| loyalty 7 | Dec 4, 2023 | $17 | sales |
| loyalty 8 | Nov 19, 2023 | $15 | sales |
| loyalty 9 | Nov 23, 2023 | $22 | sales |
| loyalty 9 | Dec 15, 2023 | coupon | sales |
I have also created some variables as the end user will select data based on the date picker dates. For the simplicity of coding other data items in my data, I have kept the date format but have also converted it to a date key value ie: Dec 2, 2023 becomes 20231203.
| =v_current_startDT |
| =v_current_endDT |
| =v_pre_startDT |
| =v_pre_endDT |
I am aggregating data based on the date picker dates. To pull all sales for the current period , I have created the following set analysis (which works perfectly): Sum({$ <coupon=,customer_id = p(customer_id),source = {"sales"}>} purchase) Note: a customer can enter a coupon value but may not have completed the purchase on the same day. The returned data is based on the date picker dates selected by the user, so the set analysis needs to filter out data where the customer only enters the coupon but does not purchase or else I will end up with duplicate counts of the loyalty customers. This is the "coupon=,customer_id = p(customer_id)" portion of the set analysis.
The app will compare the current period to the same number of days pre (based on the date picker dates) so the set analysis for the pre looks like this: sum( {$ <coupon=,customer_id = p(customer_id), source = {"sales"}, date_dt = , date_key = {">=$(=v_pre_startDT)<=$(=v_pre_endDT)"}>} purchase)
This also works, however it is including loyalty customers that have purchased in the pre but not in the current period as highlighted below. I don't want to include those highlighted customers.
I am trying to apply a condition to the pre set analysis ie: give me all sales in the pre only if the current period sales are greater than zero but each thing I try is not working correctly. Any ideas?
