Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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 |
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
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])
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
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?
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
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
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