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

Closing month Headcount calculation for all previous years - Qlik Sense Enterprise

Hi All,

I need to calculate and show the closing month headcount of previous fiscal years which should dynamically change if we select any fiscal quarter or month accordingly. But in the first look in a Bar-graph, it should only show the  Closing month i.e. march month Headcount.

Currently, I'm using the following logic:

pick(vMeasure,
Count({$<[Fiscal Month]={'Mar'}>}distinct [PS No])-Count({$<[Fiscal Month]={'Mar'},EXT_Flag={'External'}>}distinct [PS No]))

where I am getting the closing month headcount without externals(External employees) correctly but if i select other selections like Month and quarter it will not change accordingly.

Hope I explained it properly and looking forward to suggestions.

Thank you.

1 Solution

Accepted Solutions
HugoRomeira_PT
Creator
Creator

Hello,

Try like this:

Count({$<[FiscalMonthNUM]={$(=Max(FiscalMonthNUM))}>}distinct [PS No])-Count({$<[FiscalMonthNUM]={$(=Max(FiscalMonthNUM))},EXT_Flag={'External'}>}distinct [PS No])

 

Best regards

Hugo Romeira

If the issue is solved please mark the answer with Accept as Solution.
If you want to go quickly, go alone. If you want to go far, go together.

View solution in original post

8 Replies
vinieme12
Champion III
Champion III

its is not changing because you have hardcoded Month ='Mar'

if you need it to be flexible you need to remove the hardcoded month filter from set analysis

 

Change it to below

If there are selections, calculate for latest month else evaluate for Mar

=if(getselectedcount(Month) or getselectedcount(Year) or getselectedcount(Quarter)

, Count({<[Fiscal Month]={"=Max(Month)"}>}distinct [PS No])-Count({<[Fiscal Month]={"=Max(Month)"},EXT_Flag={'External'}>}distinct [PS No])

,Count({$<[Fiscal Month]={'Mar'}>}distinct [PS No])-Count({$<[Fiscal Month]={'Mar'},EXT_Flag={'External'}>}distinct [PS No])

)

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
HugoRomeira_PT
Creator
Creator

Hello,

From what I understand, your Fiscal Month field contains the Month short Name (Jan, Mar, May, etc), which is a string.


If you have any other "numeric" calendar dimension you should use it to get a max month/date logic.

If you create YearMonth field to look like 202203 YYYYMM (2022 March), you could than dinamically calculate the max available month.

 


Count({$<[YearMonth]={$(=Max(YearMonth))}>}distinct [PS No])-Count({$<[Fiscal Month]={$(=Max(YearMonth))},EXT_Flag={'External'}>}distinct [PS No])

 

Hope it helps.

 

If the issue is solved please mark the answer with Accept as Solution.
If you want to go quickly, go alone. If you want to go far, go together.
Manmeet
Contributor III
Contributor III
Author

Thank you for your response... I tried your solution, This is also giving the same result as i was getting earlier only march month data is flowing and it is not changing dynamically when i select any month or quarter for a particular fiscal year.

Let me know if you get any other solution for this. we also have  a numeric calendar dimension i.e. "MonthYear", if we can use this to get the desired solution.

 

 

Thank you

Manmeet
Contributor III
Contributor III
Author

Hi Thank you for your response we do have a calendar field "MonthYear" i tried you solution with that , It isn't working for me...Let me know if you come up with any other solution for this..

 

Thank you 

HugoRomeira_PT
Creator
Creator

I will be glad to help.

Could you please share a print with the some sample data on your calendar dimension?

Best regards

Hugo Romeira

If the issue is solved please mark the answer with Accept as Solution.
If you want to go quickly, go alone. If you want to go far, go together.
Manmeet
Contributor III
Contributor III
Author

Manmeet_0-1652441858011.png

sure , kindly check this.

 

Thank you.

HugoRomeira_PT
Creator
Creator

Hello,

Try like this:

Count({$<[FiscalMonthNUM]={$(=Max(FiscalMonthNUM))}>}distinct [PS No])-Count({$<[FiscalMonthNUM]={$(=Max(FiscalMonthNUM))},EXT_Flag={'External'}>}distinct [PS No])

 

Best regards

Hugo Romeira

If the issue is solved please mark the answer with Accept as Solution.
If you want to go quickly, go alone. If you want to go far, go together.
Manmeet
Contributor III
Contributor III
Author

Yes , I think this is working.

Thank you so much for this 🙌