topic Re: Set Analysis formula in App Development

Set Analysis formula

madnanansari — Thu, 14 Dec 2017 07:16:46 GMT

I am working with a retail system with single store.

I have sales data in the QVD file with the below columns:

DATE

INVOICE NO

ITEM

SOLD QTY

SALES AMOUNT

CUSTOMER MOBILE NO

I am using standard MASTER CALENDAR.

One of the requirement is to create customer retention report.

User has the filter for MONTH. If he selects JAN-2017 for example, I have to show how much times the customer visits us during this month (its easy). Customer is recognized by his Mobile No. The tricky part is I have to take the same Customer list (from the month which the user selected) and find out how much are those customer's (for each customer) visits during the month of Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov and Dec. Basically I need to find out who are the repeat customers.

I think that this is a Set Analysis question but I don't know how to do it.

Re: Set Analysis formula

Anonymous — Thu, 14 Dec 2017 09:28:39 GMT

Hi Mohammad,

how should you present your data? which object should you use?

Re: Set Analysis formula

sunny_talwar — Thu, 14 Dec 2017 14:10:20 GMT

Would you be able to share some raw data and provide us with the expected output from the data that you provide?

Re: Set Analysis formula

madnanansari — Sun, 17 Dec 2017 08:05:28 GMT

This would be a pivot table where cutomer mobile no comes vertically whereas months comes horizontally and the cell are filled with the number of customer visits.

Re: Set Analysis formula

madnanansari — Sun, 17 Dec 2017 08:06:41 GMT

pls. find attached a sample data file.

Re: Set Analysis formula

sunny_talwar — Sun, 17 Dec 2017 22:21:42 GMT

How about the expected output?

Re: Set Analysis formula

Anonymous — Mon, 18 Dec 2017 04:39:31 GMT

Load the table again with just the Month (As Month2) and the customer Mobile No.

Here's an example:

Code:

Output: (with month March filtered, Month2 does not change)

Hope this was helpful and you get the logic. Let me know!