expression=sum(HeadCount) should show the information for last month

[#1]

Hi,

I have historic data of headcount, i.e. headcounts for each department for each month in the last two years,

I have limited my data to the most current one by selecting a value in a field that specified the month the data was created. For example, I have ReportMonth that holds that information, just select 2014-June and a chart with dimension Department and expression=sum(HeadCount) should show the latest information.

But my problem is that this ReportMonth field is fixed in my script, and now I need to show expression =sum(HeadCount) for any Month which the user select and sumarizing just the selected month data.

Can you help me?

Best Regards

[juleshartley]

Sounds like a perfect case for set analysis...

Create a calendar table and then a variable called 'vReportMonthSelected'. Set this variable to be the report month selected by the user eg. = only(getfieldselections(ReportMonth)), then use an expression like:

sum(

{<

ReportMonth={"$(vReportMonthSelected)"}

>}

HeadCount

)

[santharubban]

can you share the application.

[#4]

I will try this and will tell you about later

Thans

[#5]

I'm sorry, I can't share my application, but data source is excel similar to following image with actual and budget data.

I have 2 years of data: actual HC (full 2013 and Jan-May 2014) and budget HC (full 2013 and full 2014)

If the user selects Year = 2014 and Month: Jan|Feb|Mar ---> I need to show them sum (HC) just for Mar-2014

Any clue?

[#6 · Accepted solution]

Finally, I created a new column in my source excel file named "Period" like 201301, 201302,...201405 (numeric) and apply the following formula:

sum({$<Period={$(=Max(Period))}>}Headcount)

And it works!