Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear experts,
I am trying to achieve this expression where I am selecting a field value called Revenue 3rd party- Contract, and dates which are in the format of P01-12
the date field is populated as such
2015 P02
2015 P03
2015 P12
2016 P02
2016 P03
2016 P12
2016 Q1 FC PM
2016 Q4 FC PM
2016 Q1 BDG
2016 Q4 BDG
the code which I am currently running is:
sum({$<BDF_Type={"Revenue 3rd Party - Contract"}, Year(Date#(date, 'YYYY PMM'))={*P*}>} cash)
Segment of code in red is the part which is giving me the error. Anyone has an idea on how to tackle this question?
Kind regards,
Royce
In set analysis, the LHS of modifier can't be an expression but a field name. What are you trying to express with the highlighted section?
Ah! I see.
what I am trying to do is to only use the dates selected the format of 2015 P01 - P12 etc.
try this
sum({$<BDF_Type={"Revenue 3rd Party - Contract"}, date={*P*}>} cash)
I doubt this will work because it might select the values with PM inside as well or will it only select P ?
yeahs I just gave it a try and it selects PM values as well
Hello Royce,
I hope that you are doing great!
As per my primary analysis, given data is at month level granularity. Therefore, you can extract the Year & Month for Date field by using -
Left(Date,4) AS Year,
If(Len(Date) = 8, Right(Date,2)) AS Month
Post that you can make revamp you existing expression.
sum({$<BDF_Type={'Revenue 3rd Party - Contract'}, Year={'2015'}, Month={'12'}>} cash)
Hope this will be helpful.
Regards!
Rahul
Try like:
Date={'*P*'} -{'PM'}
HI,
Try to create a flag in script like below
Load date_Field,if ( KeepChar(date_Field,'ABCDEFGHIJKLMNOPQRSTUVWXYZ')='P','Y','N') AS Date_flag;
LOad * Inline [
date_Field
2015 P02
2015 P03
2015 P12
2016 P02
2016 P03
2016 P12
2016 Q1 FC PM
2016 Q4 FC PM
2016 Q1 BDG
2016 Q4 BDG
];
Then Modify your expression :
sum({$<BDF_Type={"Revenue 3rd Party - Contract"}, Date_flag={'Y'}>} cash)
Cheers for the well wishes Rahul, I have tried out your solution and it works perfectly, however I picked out Tresesco's solution as it was much faster, however you have provided me some great tools to tackle my other issues I believe can be solved by your solution.
Thanks once again buddy!