Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

set analysis with clustered dates

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

1 Solution

Accepted Solutions
tresesco
MVP
MVP

Try like:

Date={'*P*'} -{'PM'}

View solution in original post

10 Replies
tresesco
MVP
MVP

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?

Not applicable
Author

Ah! I see.

what I am trying to do is to only use the dates selected the format of 2015 P01 - P12 etc.

sasikanth
Master
Master

try this

sum({$<BDF_Type={"Revenue 3rd Party - Contract"}, date={*P*}>} cash)

Not applicable
Author

I doubt this will work because it might select the values with PM inside as well or will it only select P ?

Not applicable
Author

yeahs I just gave it a try and it selects PM values as well

rahulpawarb
Specialist III
Specialist III

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

tresesco
MVP
MVP

Try like:

Date={'*P*'} -{'PM'}

sasikanth
Master
Master

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)

Not applicable
Author

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!