topic Re: set analysis help! in QlikView

set analysis help!

Wed, 25 Nov 2020 16:16:04 GMT

count({< ReleaseYear = {"2010" or "2011" or "2012"} - ReleaseYear = {"2013", "2014", "2015"}>} [ProductName])

I am trying to do a distinct count of productnames where it has release years of 2010 or2011or 2012 but do not have releases 2013or 2014or2015. How can I write this up??

Thanks!

Re: set analysis help!

sunny_talwar — Thu, 04 Aug 2016 19:27:08 GMT

May be this:

Count({<ReleaseYear = p({<ReleaseYear = {2010, 2011, 2012}>}) - p({<ReleaseYear = {2013, 2014, 2015}>})>} [ProductName])

Re: set analysis help!

Thu, 04 Aug 2016 19:32:23 GMT

Hi Sunny,

the product that I want to filter out is the one that has releases in 2010 OR 2011 OR 2012 and does not have releases in year 2013 OR 2014.

Does your script say the OR part or does it work as 2010 AND 2011 AND 2012?

Thank you for answering!!

Re: set analysis help!

sunny_talwar — Thu, 04 Aug 2016 19:33:36 GMT

Its OR

Re: set analysis help!

Thu, 04 Aug 2016 19:42:14 GMT

Hi Sunny,

This did not run from my Qlikview.

Re: set analysis help!

Thu, 04 Aug 2016 19:43:58 GMT

will try one more time!!

Re: set analysis help!

Thu, 04 Aug 2016 20:00:19 GMT

still not working....I have one question. what does P do in your script?

Re: set analysis help!

sunny_talwar — Thu, 04 Aug 2016 20:31:31 GMT

Would you be able to share a sample to take a look at?

Best,

Sunny

Re: set analysis help!

Thu, 04 Aug 2016 21:52:24 GMT

productname	Release	Year_Dt
A	2010	2010
A	2010	2010
A	2010	2011
A	2010	2011
A	2010	2012
A	2011	2012
A	2011	2012
A	2011	2012
A	2011	2012
A	2011	2012
A	2011	2011
A	2011	2011
A	2012	2012
A	2012	2012
A	2012	2012
B	2010	2010
B	2010	2011
B	2010	2012
B	2010	2012
B	2010	2011
B	2011	2011
B	2011	2011
B	2011	2011
B	2011	2011
B	2011	2012
B	2011	2012
C	2010	2010
C	2010	2010
C	2010	2011
C	2010	2012
C	2010	2012
C	2010	2011
C	2010	2011
C	2010	2012
C	2012	2012
C	2012	2012
C	2012	2012
C	2012	2012

I just made up a mock data for you. year_Dt is the year that data was retrieved. and there are people who are using 2010 release even though the time that data is retrieved is 2012.

So Let's say products which released in 2010,2011 processes their data through old platform and products and products which released in 2012 are using new platform to process data.

so even among product A, A 2010 and A 2011 is using old platform and A2012 is using new platform.

this is exactly what I want to see.

in year 2010, all A, B, C were using 2010 releases which mean they are using old platform. so all A, B, C should be visible in the chart since they have releases of 2010 or 2011 but no releases after 2012

in year 2011 it should be the same since they all have releases in 2010 or 2011 but not including and after 2012

but in year 2012, product A and C has releases in 2010 or 2011 and also in 2012. so it should not be visible.

but B has releases in 2010 or 2011 but no releases after 2012. so it should be visible.

This way, we will be able to visulize the target audience and tell product B to release new version as soon as possible that uses new platform to process its data.

Sunny, Thank you for reading through this. Is this enough explanation for you? thanks

Re: set analysis help!

Fri, 05 Aug 2016 00:05:49 GMT

productname	Release	Year_Dt
A	2010	2010
A	2010	2010
A	2010	2011
A	2010	2011
A	2010	2012
A	2011	2012
A	2011	2012
A	2011	2012
A	2011	2012
A	2011	2012
A	2011	2011
A	2011	2011
A	2012	2012
A	2012	2012
A	2012	2012
B	2010	2010
B	2010	2011
B	2010	2012
B	2010	2012
B	2010	2011
B	2011	2011
B	2011	2011
B	2011	2011
B	2011	2011
B	2011	2012
B	2011	2012
C	2010	2010
C	2010	2010
C	2010	2011
C	2010	2012
C	2010	2012
C	2010	2011
C	2010	2011
C	2010	2012
C	2012	2012
C	2012	2012
C	2012	2012
C	2012	2012

There are products A B and C

reach comes with different releases

Year_dt is the time that the data was retrieved.

any releases that is 2010, 2011 uses old platform to process its data

release 2012 uses new platform

so Product A 2010 and 2011 uses old platform and Product A 2012 uses new platform

I want to visualize the product that is using old platform and have not employed new platform.

in order to do that I can use a logic that filters for product that has releases in either 2010 or 2011 but do not have release in 2012.

the benefit of that is since I only see product B in the value, I can go and suggest them to come out with new release so that they will be using new platform also.

How would you create a logic in an expression?

I hope this is an enough explanation for you.

Thank you Sunny!

Re: set analysis help!

sunny_talwar — Fri, 05 Aug 2016 00:25:29 GMT

I am still trying to understand, will let you know if I have questions

Re: set analysis help!

sunny_talwar — Fri, 05 Aug 2016 02:03:39 GMT

This?

Script:

LOAD AutoNumber(productname&Year_Dt) as Key,

productname,

Release,

Year_Dt

FROM

[https://community.qlik.com/thread/228002]

(html, codepage is 1252, embedded labels, table is @2);

Expression:

Count(DISTINCT {<Key = {"=Max(Release) <> 2012"}>} productname)

Re: set analysis help!

sunny_talwar — Fri, 05 Aug 2016 02:09:04 GMT

Or may be you can use this expression:

Count(DISTINCT {<Key = {"=Max(Release) < 2012"}>} productname)

Re: set analysis help!

Fri, 05 Aug 2016 08:06:07 GMT

we can use this expression in set analysis

Count({<Year=-{2013,2014,2015}>},Prouductname)