Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good everyone.
I want to calculate the average number of employees in the company in such a way that the average value of the previous month+the current month divided by two is recorded in each current month.
For example,
Months | Quantity
January | 45
February | 48
March | 50
April | 49
As a result, the Headcount will be the following:
| January | February | March | April
Headcount | 45 | (45+48)/2 | (48+50)/2 | (50+49)/2
Tell me how you can implement it and where it is better to make the calculation: in the download script or on the dashboard?
you can do it both, but in frontend the expression would be something like this:
if(Months='January'
,sum(Quantity)
,(above(sum(Quantity))+sum(Quantity))/2)
Thanks Jochem_zw for your answer, but in this case, the above function hides the first value, which is not very suitable for solving the problem
Why I cannot use the similar condition:
If(Month(Date_Per) = 'Jan' and Year(Date_Per)='2019', Count(Distinct FIO),
If(Date_Per> AddMonths(Date_Per,-1) and MonthName(Date_Per, 'MMM-YYYY')<>'Jan-2019'
(Count(Distinct FIO)+Count(Distinct {<Month={'$(=Month(AddMonths(Date_Per,-1)))'}>} FIO))/2
)
)
Have you tried the Peek() function?
‘(Peek(quantity ) + quantity) / 2 AS Quantity‘ (in the script). You can also use peek() in the front end in a simple expression.
... to be clear, you’d use the peek() function as the second part of the IF() statement.