topic Re: HeadCount Calculation in QlikView

HeadCount Calculation

anat — Wed, 25 Nov 2020 16:16:04 GMT

	Month	Apr-14	May-14	Jun-14	Jul-14	Aug-14	Sep-14	Oct-14	Nov-14	Dec-14	Jan-15	Feb-15	Mar-15
Total HC		1	6	11	16	21	26	31	36	41	46	51	56

if i select Year 2014-15 it should display year end month data(Mar-15)= 56

same if i select Q1 then output = 11(Q1 end month :June-14 data)

for Q2 output= 26(Q1 end month :Sep-14 data)

for Q3 then output= 41(Q1 end month dec-14 data)

if i select Q4 then output= 56(Q1 end month :Mar-15 data)

Q1=Apr-14 to Jun-14

Q2=Jul-14 to Sep-14

Q3=Oct-14 to Dec-14

Q4-Jan-15 to Mar-15 and Year=2014-15

Re: HeadCount Calculation

ajsjoshua — Tue, 24 May 2016 11:47:20 GMT

Hi,

check this

YTD (Year-To-Date) Sales:

Sum({$<MonthID = {“<=$(=Max(MonthID))”},

Year = {$(=Max(Year))},

Quarter = ,

Month = >} Sales)

QTD (Quarter-To-Date) Sales:

Sum({$<MonthID = {“<=$(=Max(MonthID))”},

QuarterID = {$(=Max(QuarterID))},

Year = ,

Quarter = ,

Month = >} Sales)

MTD (Month-To-Date) Sales:

Sum({$<MonthID = {$(=Max(MonthID))},

Year = ,

Quarter = ,

Month = >} Sales)

Re: HeadCount Calculation

chetansehgal — Tue, 24 May 2016 12:48:19 GMT

Hi Ananth,

If you are always selecting a Year or Qtr then the following formula will always give you the sales of the Max month of selected(current) Year or Qtr. Also you need to create the Qtr field based on the fiscal year in the calendar script.

=Sum({<MonthID={$(=Max(MonthID))>}Sales)

or you can try below formula

=Sum({<Month={$(=Month(Max(Date)))}>}Sales)

But above formulas will give 0 if there is no entry for the latest month.

Thanks,

Chetan