New Customers Sales by Clicking Year and Month

[renjithk]

Hi All,

I want to Create a Pivot table where its shows the customer category, sales on new customer, profit on new customers.

The Client will select the Year and Month, based on that the data should be filtered, like if the client select the Year 2018 and month Jan, the pivot table should be filtered based on the  Selected month and Last Month. That is  Comparing 2017 Dec and 2018 Jan and provide How Much Sales Earned by the new Customers (who are not in Dec-2017).

Please Advise How can i Achieve This.

The Data Model Include Customer category, Customer Code, Invoice Date ,Sales and profit.

[kaushiknsolanki]

Hi,

Try this.

Load "Customer category", "Customer Code", Date("Invoice Date") as "Invoice Date",Sales ,Profit inline [
Customer category, Customer Code, Invoice Date ,Sales ,Profit
1,A1,43101,100,10
1,A1,43132,110,15
1,A1,43160,120,20
2,A1,43132,300,30
2,A1,43160,350,60
1,A2,43132,115,15
2,A2,43132,310,50
2,A2,43160,320,70
1,A3,43160,140,30
2,A3,43160,370,50
];

Now create a chart with Customer Code as dimension and Below expression to get Sales of new customer.

Sum({<[Customer Code] = e({<[Invoice Date] = {"$(=Addmonths(Max([Invoice Date]),-1))"}>}), [Invoice Date] = {"$(=Max([Invoice Date]))"}>}Sales)

Similarly you can get Profit.

Regards,

Kaushik Solanki

[renjithk]

Hi,

i have tried with the below and  received something like this 

selected 2019 Aug, it shows the below customer, but for this customer there is Sales in 2019 Jul, so they cannot be here.

But all the invoice values of this customer is not taken, only the invoices done on 31-Aug-2019 is taken as new

Could you advise 

[Brett_Bleess]

Renjith, the best way to get help on these types of posts is to be sure to include your QVW or a sample one that has similar data model etc., so folks can see the actual data model and then look at the chart settings to be sure they provide the correct guidance.

Regards,
Brett