Totals Volumes by FY split by Month

[thomas_2583]

Hi,

I have a bar chart which shows the total volume of customer acquired within the Financial Year. 

Dimension: Date_FY_Year

Measure: count(distinct{<[New_Customers.Purchase_Flag]={'1'}>} CustomerID)

Above works fine and for FY19/20, I get 268 customers which is correct. 

What I now want to see out of those 268 Customers, how many had a Purchase_Flag = 1 split by Month within the FY (April to December is the current data for FY19/20). 

I have a month variable Date_FY_Month but when I change my logic to below, my volumes look nothing like what I should be getting and I'm not too sure why, maybe it's totalling all Financial Years, do I need to amend my logic at all?

Dimension: Date_FY_Month

Measure: count(distinct{<[New_Customers.Purchase_Flag]={'1'}>} CustomerID)

I hope this makes sense.

[teiswamsler]

Hi Thomas

This should work if the data model contains transactions for every CustomerId each month.

Sum( Aggr( count(distinct{<[New_Customers.Purchase_Flag]={'1'}>} CustomerID), Date_FY_Month ) )

Br.
Teis

[Kushal_Chawda]

When you say you get 268 count for current FY, Do you select the current FY  value?

[thomas_2583]

Yes, I have a bar chart that is split by FY so when I select FY 1920 within that bar chart, I would then expect to see the bar chart split by month react to the selection. 

[thomas_2583]

Thank you for your suggestion but that has made any difference, the numbers still show the same as the logic I had used originally. 

[Kushal_Chawda]

If you select a particular year and your month is associated with Year then values should automatically reflect for Monthly split by Year. 

[thomas_2583]

I agree which is why I am so confused by this.

[Kushal_Chawda]

Would you be able to share the sample file to look at?

[thomas_2583]

I'm not allowed to, sorry.

[thomas_2583]

I am looking into the data and it appears that the Purchase_Flag = 1 stays with the Customer so if they have a Purchase_Flag = 1 in April, this will show in May, June, July, August, September, October, November. 

If another customer has a Purchase in say August, it will show for August, September, October and November. 

On these 2 example, I would want to see a volume of 1 for April only and a volume of 1 for August only so essentially the first month whereby we have a 1.

Is there another way to write the logic to show only one record regardless of future months? I thought the distinct function would have done this but for a reason unknown to me, it's not.