topic Re: set analysis with clustered dates in QlikView

set analysis with clustered dates

Fri, 24 Feb 2017 06:37:38 GMT

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

Re: set analysis with clustered dates

tresB — Fri, 24 Feb 2017 07:20:40 GMT

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?

Re: set analysis with clustered dates

Fri, 24 Feb 2017 07:24:07 GMT

Ah! I see.

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

Re: set analysis with clustered dates

sasikanth — Fri, 24 Feb 2017 07:25:01 GMT

try this

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

Re: set analysis with clustered dates

Fri, 24 Feb 2017 07:28:33 GMT

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

Re: set analysis with clustered dates

Fri, 24 Feb 2017 07:30:52 GMT

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

Re: set analysis with clustered dates

rahulpawarb — Fri, 24 Feb 2017 07:32:59 GMT

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

Re: set analysis with clustered dates

tresB — Fri, 24 Feb 2017 07:38:48 GMT

Try like:

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

Re: set analysis with clustered dates

sasikanth — Fri, 24 Feb 2017 07:40:20 GMT

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)

Re: set analysis with clustered dates

Fri, 24 Feb 2017 07:56:30 GMT

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!

Re: set analysis with clustered dates

rahulpawarb — Fri, 24 Feb 2017 08:40:29 GMT

Thank you Royce for kind words!

Cheers,

Rahul