Results of measure using set analysis is not as per dimension

[Shilpa16]

Hi Friends

I need your help. I have a requirement where I need to show the count of orders which are placed by different customers in different months in a table . I need to consider only those customers who are in target(Target_Flag=1) on each monthend date (like 30th june for the month of june)and then count the orders placed by these customers during that month.

For example:

Example Dataset:

Cust_Id	Order_Id	DateId	Target_Flag
1	55	11-06-2019	1
1	73	21-06-2019	0
1	40	30-06-2019	1
1	28	04-07-2019	0
1	75	18-07-2019	1
1	45	21-07-2019	1
1	50	31-07-2019	0
2	55	16-06-2019	1
2	10	30-06-2019	0
2	35	07-07-2019	0
2	50	31-07-2019	1
3	25	05-06-2019	1
3	44	23-06-2019	1
3	20	30-06-2019	1
3	50	15-07-2019	0
3	25	27-07-2019	0
3	30	31-07-2019	0

Required result in table:

MonthYear	Orders count	Explanation
Jun-2019	6	Cust_Id=1 and 3(Target_Flag=1 on 30th june)
Jul-2019	2	Cust_Id=2(Target Flag =1 on 31st jul

So we need to consider the Target flag only on the monthend and based on that we have to count all the order_Id placed by that target cutomer during that month irrespective of the flag when the order was placed.

I am using the following expression at the moment for the same:

Aggr(count({<Cust_Id = P({<[Target_Flag] = {1},  Daily_Flag= {'M'} >})>} distinct Order_Id), MonthYear)

Daily_Flag is a flag  which I created to give  M for Monthend dates and D for rest other dates.

It is giving me wrong values when I select the MOnthYear filter then it gives me correct value. I want to see the correct value even without the month filter. Please suggest if somebody has a solution.

Thanks in Advance,

Shilpa

[UserID2626]

Remove count({<Cust_Id = P({<[Target_Flag] = {1},  Daily_Flag= {'M'} >})>} distinct Order_Id) in dimension.

In dimension just give [MonthYear] and measure = count({<Cust_Id = P({<[Target_Flag] = {1},  Daily_Flag= {'M'} >})>} distinct Order_Id)

[JordyWegman]

Hi Shilpa,

I don't really get your sample, but if you want to count the montends with a target flag to this:

Create a concatenated key based on Customer &'|'& Order (%CustOrder)

Use this formula:
Count( {$< Target_Flag = {1}, %CustOrder = {"=Floor(Date#(DateId,'DD-MM-YYYY')) = Floor(MonthEnd(Floor(Date#(DateId,'DD-MM-YYYY')))) "} >}Cust_Id &'|'&Order_Id)

Outcome:

Jordy

Climber

[Shilpa16]

Hi,

Many thanks for your reply.

My dimension is MonthYear and my measure is 

Aggr(count({<Cust_Id = P({<[Target_Flag] = {1},  Daily_Flag= {'M'} >})>} distinct Order_Id) ,MonthYear)

but this is not giving the desired result. I tried using your expression

count({<Cust_Id = P({<[Target_Flag] = {1},  Daily_Flag= {'M'} >})>} distinct Order_Id)

but still the same. Actually it is counting all the Cust_Id in that month who placed the order and were in target at any point of time not necessarily in that monthend. As sson as apply the filter for MonthYear it gives the correct result.