Pick and Match not working in set analysis

[Shilpa16]

Hi Experts,

I need your expert advice again. 

I am working on an app where I need to count the products sold to  customers  which is calculated based on the customers who are flagged as target on the monthend(latest selected monthend) and then I have to count all the products sold to those customers in the time period selected(user can select multiple months ).Example:

If the user select 3 months say July 2019, Aug 2019,Sep 2019, then we have to check the customers who are flagged as Target on the last day of last selected month i.e. 30 sep 2019 and then I have to count all the products sold to those Target customers in these 3 months(July,Aug,Sep).

To implement the same  I have created variables -

vCust_Monthend : Stores the monthend Target Cust_Id concatenated with date  and it stores only the monthend dates.

vCust_List: Stores the Target Cust_Id at the monthend (derived from the same table above).

I have written the following expression:

Count({<Cust_Id = {pick(match(Cust_Id & '-' & max(DateId), $(vCust_Monthend)),$(vCust_List))}>}distinct Prod_Id) 

Example Dataset:

Cust_Id	Prod_Id	DateId	Target_Flag
1	55	30-06-2019	1
1	73	07-07-2019	0
1	40	31-07-2019	0
1	28	25-08-2019	1
1	75	31-08-2019	1
2	45	21-06-2019	0
2	50	31-07-2019	0
2	55	16-08-2019	1
2		31-08-2019	0
3	35	20-06-2019	0
3	70	28-07-2019	1
3		31-08-2019	1

In the sheet I am putting a filter  say July and Aug (2019) so we have to consider only Customer 1 and 3 since they have Target flag as 1 on 31 Aug and then we have to count the Products sold (only rows with non null prod id ) to them in these two months irrespective of their target flag in between.So my expected result is as follows:

KPI should show 5(as highlighted in green in the above table)

Could you please suggest where I am going wrong?

Thanks,

Shilpa

[JGMDataAnalysis]

Script:

SET NullInterpret = '-';

Test:
NOCONCATENATE
LOAD 
	*, 
	Date(Date#(Date, 'DD-MM-YYYY')) AS DateID
INLINE [
        Cust_Id, Prod_Id, Date,	  	  Target_Flag
          1,	 55,	  30-06-2019, 1
          1,	 73, 	  07-07-2019, 0
          1,	 40,	  31-07-2019, 0
          1,	 28,	  25-08-2019, 1
          1,	 75,	  31-08-2019, 1
          2,	 45,	  21-06-2019, 0
          2,	 50,	  31-07-2019, 0
          2,	 55,	  16-08-2019, 1
          2, 	 -,	 	  31-08-2019, 0
          3,	 35,	  20-06-2019, 0
          3,	 70,	  28-07-2019, 1
          3, 	 -,	 	  31-08-2019, 1
];

DROP FIELD Date;

Measure:

Count({<[Cust_Id] = P({<[Target_Flag] = {1}, [DateID] = {"$(=MonthEnd(Max([DateID])))"}>}), [Prod_Id] = {"=Len(Trim([Prod_Id])) > 0"}>} Distinct [Prod_Id])

[Shilpa16 · Accepted solution]

Hi,

Thanks for your reply. I tried using the measure that you have given but its not giving me the correct result, it is giving 0 always whatever month I select.  I tried using Aggr function as well but that also didn't give me the correct result. Could you guide me if there is any other way to get the required result.

Thanks,

Shilpa

[JGMDataAnalysis]

What is the expression you use to define the Month field?

Could you share an image of the data model or the script you are using?

[Shilpa16]

Hi,

I am using MonthYear as Month field with the following format : Oct-2019. Also I have DateId field with the format 01/10/2019. So when I select the MonthYear filter I need to identify the customer which are in target for that monthend ad then I have to go back and count the products sold to those customers for the selected month/months.

The data model is very complex and this is just a part of requirement. 

Many Thanks,

Shilpa

[Shilpa16]

Hi,
I  just little bit modified the expression that you gave and it is working. So the final working measure is as follows:

Count({<[Cust_Id] = P({<[Target_Flag] = {1}, [DateId] = {"$(=(Max([DateId])))"}>})>} Distinct [Prod_Id])

Thank you so much for your help.

Regards,

Shilpa

[Shilpa16]

Hello Everyone,

Thanks once again for my last post  solution. Here I am again struggling on the same kind of problem where I need to count the orders placed by customers during  a month whose target flag was one at that monthend. Ex if my Cust_Id=1, has the Target_Flag=1 on the last day of a month say 31st sep 2019 , then I need the count of orders placed by that customer during the sep month .So I am creating a table where my dimension is MonthYear in the format(Oct-2019) and I am writing one measure expression as :

num(Count({<[Cust_Id] = P({<[Target_Flag] = {1}, [DateId] = {"$(=floor(Monthend(MonthYear)))"}>})>} Distinct [Order_Id]), '#,##0')

My table looks like with the current expression:

MonthYear	Orders
Sep-2019	0
Oct-2019	0

my desired result is:

MonthYear	Orders
Sep-2019	15
Oct-2019	20

This expression is giving me 0 without any month selection but as soon as I select Month filter I get the desired value.

Could you please suggest what I am doing wrong.

Many Thanks,

Shilpa