topic Re: Count where present in a quarter and not in another one in Connectivity & Data Prep

Count where present in a quarter and not in another one

gianniko — Tue, 28 May 2019 15:44:06 GMT

Hello,

I am having a lot of difficulties with a chart I would like to create. I have 4 quarters and I need to count the number of new enterprises between different quarters as well as the closed enterprises. To do so, if the enterprise is in the first quarter and not in the second, it means that it has closed. On the contrary, if it is not present in the 1st quarter and it appears in the second one, it means it is a new one.

I wanted to be able to choose any 2 quarters and get the result of new/closed enterprises.

If you have any ideas, I will be more than graterful.

Re: Count where present in a quarter and not in another one

dplr-rn — Wed, 29 May 2019 13:34:20 GMT

Please share some sample data fr us to understand what you are working with. Also share a expected result examples

Re: Count where present in a quarter and not in another one

Vegar — Wed, 29 May 2019 13:51:27 GMT

Maybe you could do something like this in a object with two selected quarter as dimension.

Closed enterprises:
=Count({< EnterpriseID=E({<QuarterID ={"$(MAX(QuarterID)"}>}) >}
EnterpriseID)

Started enterprise:
=Count({< EnterpriseID=E({<QuarterID ={"$(MIN(QuarterID)"}>}) >}
EnterpriseID)

Re: Count where present in a quarter and not in another one

gianniko — Wed, 29 May 2019 15:05:13 GMT

Here is the excel file with all the registration number of the enterprise and its quarter (there are 4 different quarters here).

I extracted from the excell file 4 tables in postgresql for eacher quarter. Then I created views that select the enterprise present in one table and not in the other one. For instance, this one select the enterprises closed from the 1st quarter to the 2nd qurter.

I want to do this in QlikSense and put the result in a Sheet and then count the number of those close/new enterprises..

Re: Count where present in a quarter and not in another one

gianniko — Thu, 30 May 2019 09:55:04 GMT

But in QlikSense you cannot choose date..

What is the 'E' referred in your request?

Re: Count where present in a quarter and not in another one

Vegar — Mon, 03 Jun 2019 09:05:45 GMT

E() is refering to excluded values in SET Analysis. See this help article: Set modifiers with implicit field value definitions

I did an example app from your excelfile. See attached qvf file.

Re: Count where present in a quarter and not in another one

gianniko — Wed, 19 Jun 2019 11:09:06 GMT

Thank you for your example but I don't really understand what it actually does and I don't think it is what I am looking for. Your example with the max string, it calculates the closed enterprises but between which quarter? Because I wanted to calculate the closed enterprises between the 1st and 2nd quarter, than between the 1st and the 3rd, than the 1st and the 4th, then the 2nd and the 3rd, etc...

Re: Count where present in a quarter and not in another one

Vegar — Wed, 19 Jun 2019 11:52:47 GMT

I where not counting correctly, I needed to use distinct as there where more than one transaction per month.

Try to look at the updated new example app and my screenshots below.

Q2-Q3

Q2 - Q4

Q3 - Q4

Re: Count where present in a quarter and not in another one

gianniko — Thu, 20 Jun 2019 08:05:49 GMT

I am afraid as you can see below, the results are not really correct, the 2 sheets below are the results imported from PostgreSQL so I guess they are the correct results. You have for some (especially the closed one) some major differences.. I don't know why...

Re: Count where present in a quarter and not in another one

gianniko — Thu, 20 Jun 2019 08:22:03 GMT

Oh my.. I think it is actually working, not for every quarter though but for most of them thank you SO much!!!!

Re: Count where present in a quarter and not in another one

gianniko — Thu, 20 Jun 2019 08:27:04 GMT

Just a quick last question isn't it the contrary: when you put MAXString it is for the closed enterprise? Like if is they are present in the 1st and not the 2nd it means it is closed.

Re: Count where present in a quarter and not in another one

Vegar — Thu, 20 Jun 2019 12:52:00 GMT

Yes, that was the idea.

Re: Count where present in a quarter and not in another one

gianniko — Wed, 26 Jun 2019 07:23:42 GMT

Just a quick other question, I want to put a condition "if" in the request . I want to make a fusion of the two lines below, which mean counting the new enterprises only if the municipality name is ΧΕΡΣΟΝΗΣΟΥ

Count(distinct {< [mitroo-03072017.Registration_Number] *=E({<[mitroo-03072017.Date] ={"$(=MINstring({<[mitroo-03072017.Registration_Number]>}[mitroo-03072017.Date]))"}>}) >} [mitroo-03072017.Registration_Number])

if ([Municipality_Unit-municipality_name]= 'ΧΕΡΣΟΝΗΣΟΥ')

I tried :

Count(distinct if ([Municipality_Unit-municipality_name]= 'ΧΕΡΣΟΝΗΣΟΥ', {< [mitroo-03072017.Registration_Number] *=E({<[mitroo-03072017.Date] ={"$(=MINstring({<[mitroo-03072017.Registration_Number]>}[mitroo-03072017.Date]))"}>}) >}) [mitroo-03072017.Registration_Number])

without success

Thank you!

Re: Count where present in a quarter and not in another one

Vegar — Wed, 26 Jun 2019 09:09:45 GMT

You could probably expand your set modifier like this for handling the 'ΧΕΡΣΟΝΗΣΟΥ'

=Count(distinct {< [Municipality_Unit-municipality_name]= {'ΧΕΡΣΟΝΗΣΟΥ'}, [mitroo-03072017.Registration_Number] *= E({<[mitroo-03072017.Date] ={"$(=MINstring({<[mitroo-03072017.Registration_Number]>}[mitroo-03072017.Date]))"}>}) >} [mitroo-03072017.Registration_Number])

-Vegar

Re: Count where present in a quarter and not in another one

gianniko — Wed, 26 Jun 2019 09:48:44 GMT

thank you very much!