Solved: Count where present in a quarter and not in anothe... - Qlik Community

gianniko · ‎2019-05-28

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.

Vegar · ‎2019-06-19

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

Vegar
Qlik Community MVP

View solution in original post

Vegar · ‎2019-06-26

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

Vegar
Qlik Community MVP

View solution in original post

dplr-rn · ‎2019-05-29

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

Vegar · ‎2019-05-29

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)

Vegar
Qlik Community MVP

gianniko · ‎2019-05-29

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..

gianniko · ‎2019-05-30

But in QlikSense you cannot choose date..

What is the 'E' referred in your request?

Vegar · ‎2019-06-03

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.

Vegar
Qlik Community MVP

gianniko · ‎2019-06-19

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...

Vegar · ‎2019-06-19

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

Vegar
Qlik Community MVP

gianniko · ‎2019-06-20

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...

gianniko · ‎2019-06-20

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

Count where present in a quarter and not in another one