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: 
gianniko
Contributor III
Contributor III

Count where present in a quarter and not in another one

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.

2 Solutions

Accepted Solutions
Vegar
MVP
MVP

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

image.png

 

 

 

Q2 - Q4

image.png

Q3 - Q4

image.png

View solution in original post

Vegar
MVP
MVP

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

View solution in original post

14 Replies
dplr-rn
Partner - Master III
Partner - Master III

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

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)
gianniko
Contributor III
Contributor III
Author

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

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
Contributor III
Contributor III
Author

But in QlikSense you cannot choose date..

What is the 'E' referred in your request?
Vegar
MVP
MVP

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.

image.png

gianniko
Contributor III
Contributor III
Author

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

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

image.png

 

 

 

Q2 - Q4

image.png

Q3 - Q4

image.png

gianniko
Contributor III
Contributor III
Author

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...new.pngclose.png

gianniko
Contributor III
Contributor III
Author

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