Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am very vew to Qlik view.
I want to create a line chart which should show the total number of employees in each fiscal year.
The fiscal year starts from July-June. I have data from 2011-June to 2017-September.
Currently I have data from fiscal year 2011-2012, 2012-2013- 2013-2014, 2014-2015, 2015-2016, 2016-2017 and 2017-2018
2017-2018 is the currently running fiscal year.
II wrote an expression
=sum(
if(FiscalMonth=12,NumberEmployees)
)
it is giving me the correct number of employee count in all the fiscal year except 2017-2018 ( which started from July 2017)
My problem is the chart is not showing the max month of the currrent year value. it is showing upto 2016-2017
I have a field called FiscalYear and FiscalMonth in my datawarehouse
My logic is this:
If There is a fiscalMonth=12 ( June-which is the last month of our fiscal year) show the total number of employees in June
If the fiscal year is not completed , show the number of employees in the most current month (In this case Sepetember 2017)
Please help me to find the solution.
Thank you very much for your time and help
How about this?
If(fiscalmonth=12 and fiscalmonth = Max(TOTAL fiscalmonth), Count({<fiscalmonth = {'$(=Min(fiscalmonth))'}>} employees), count({<fiscalmonth = {'$(=Month(Today()))'}>} employees))
OR
Sum(Aggr(If(fiscalmonth=12 and fiscalmonth = Max(TOTAL fiscalmonth), Count({<fiscalmonth = {'$(=Min(fiscalmonth))'}>} employees), count({<fiscalmonth = {'$(=Month(Today()))'}>} employees)), fiscalmonth, fiscalyear))
Thanks Anil for the reply.
Second function is not working and the first function is summing up all the values in each month.
Below is the number of employees in each month. I wanted to find the total number of employees in each fiscal year . For example, 2011-7 to 2012-6 is the fiscal year 2011-2012. The number of employees in each month varies as the employees resign from the company . The dimension is the FiscalYear. Would this explanation be helpful to find the solution
CYear-Month | Number of Employees |
2011-7 | 1535 |
2011-8 | 1533 |
2011-9 | 1544 |
2011-10 | 1565 |
2011-11 | 1579 |
2011-12 | 1584 |
2012-1 | 1592 |
2012-2 | 1601 |
2012-3 | 1582 |
2012-4 | 1612 |
2012-5 | 1630 |
2012-6 | 1627 |
2012-7 | 1640 |
2012-8 | 1652 |
2012-9 | 1678 |
2012-10 | 1671 |
2012-11 | 1694 |
2012-12 | 1679 |
2013-1 | 1692 |
2013-2 | 1708 |
2013-3 | 1706 |
2013-4 | 1699 |
2013-5 | 1761 |
2013-6 | 1764 |
Thank you very much for your help