Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi community,
I have a pivot table that has 12 expressions representing the employee counts by month of the organization. For now this is how I have to represent the data as the set analysis to calculate the employee counts is very complicated.
Is there anyway that someone can think of that I could turn these expressions into the points on a line chart.
Is the a way I can make a chart that would allow me to see the employee counts by month graphically rather than the the straight or pivot table?
It's an ugly solution, but it should fly on a moderate data set...
Create a Dimension for 12 months and a single expression that will pick one of your 12 calculations based on the dimension value - something like:
Pick(Month, Expr1, Expr2, ... Expr12)
This way, you can present a single Dimension with a single Expression in a line chart
cheers,
Oleg
Hi Oleg,
My expressions are below with MonEnd3 being replaced by MonEnd4 etc. For dimension I use CurrMon3 which is tied to MonEnd field.
SUM(AGGR(count({(<terminationdate = {">=$(=only(MonEnd3))"},
employmentdate ={"<=$(=only(MonEnd3))"}>)+(<terminationdate = {'-693593'},
employmentdate ={"<=$(=only(MonEnd3))"}>)-<terminationdate = {'-693593'},
employmentdate ={'-693593'}>}
distinct PRMNAM),PRMDDP))
Would anything in my expression prevent the pick() function from working as I don't get a line chart to show.
=
Pick(CurrMon3,SUM(AGGR(count({(<terminationdate = {">=$(=only(MonEnd3))"},
employmentdate ={"<=$(=only(MonEnd3))"}>)+(<terminationdate = {'-693593'},
employmentdate ={"<=$(=only(MonEnd3))"}>)-<terminationdate = {'-693593'},
employmentdate ={'-693593'}>}
distinct PRMNAM),PRMDDP)),SUM(AGGR(count({(<terminationdate = {">=$(=only(MonEnd4))"},
employmentdate ={"<=$(=only(MonEnd4)"}>)+(<terminationdate = {'-693593'},
employmentdate ={"<=$(=only(MonEnd4))"}>)-<terminationdate = {'-693593'},
employmentdate ={'-693593'}>}
distinct PRMNAM),PRMDDP)))
,...
)