Help with Complex Expression

[#1]

Hi everyone,

I am struggling to create the following expression to be used in a chart.

I have a concatenated fact table which consists of Sales (record type 'SA') and First Sales Date (record type 'FS') which consists of a date when the customer had the first sale.

I want to create an expression where I get the total Sales for the month where the customers had the first Sales.

For ex for April 2016:
Total Sales in month  $15,000
No of First Customers 230
Total Sales from first customers $8,500 (therefore Sales from returning customers $6,500)

I wrote the following expression:
=SUM({<AccountID=P({<Type={'FS'>}) >} Sales)

however the above will give me the sales of months 5,6,7, etc.. for the customers who came first active in Month 4, and not sales for just month 4.

Can anyone help me in this please?

I want to somehow achive the following condition
=SUM({<AccountID=P({<Type={'FS'}, Month_Number_Year={'4'}>} ), Month_Number_Year={'4'}>} Sales)

Many thanks for your help,

Jon

Message was edited by: Jonathan Mallia

[#2]

Thanks Sunny, Both provide the same result actually. Are the 2 expressions syntactically identical? You recon is there a better or cleaner way to write the expression? Many thanks again, Jon

[sunny_talwar · Accepted solution]

I think the two expressions are different ways to reach same result but can vary based on your actual data. In your case if both the expressions are working, I would suggest you to use your own expression as it is simpler and require a smaller aggregation.

HTH

Best,

Sunny

[jcmachado]

You can use the following set expression to calculate the total sales for the month where customers had their first sale:

Sum({$<[First Sales Date.autoCalendar.Month]={"$(=Month([First Sales Date]))"}>} Sales)

This expression uses the "Sum" function to sum the sales for the selected month, specified by the First Sales Date field. The "First Sales Date.autoCalendar.Month" field is used to filter the data to the month of the first sale. The "Month" function is used to extract the month from the First Sales Date field.

To calculate the number of first customers, you can use the following expression:

CountD(Distinct {$<[First Sales Date.autoCalendar.Month]={"$(=Month([First Sales Date]))"}>} [Customer ID])

This expression uses the "CountD" function to count the number of distinct customer IDs in the selected month, specified by the First Sales Date field.

You can use these expressions in a chart, and then subtract the Total Sales from first customers from the Total Sales in the month to get the Sales from returning customers.