Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

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

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


1 Solution

Accepted Solutions
Not applicable

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

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!

5 Replies
juleshartley
Valued Contributor

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

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
Contributor II

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

can you share the application.

Not applicable

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

I will try this and will tell you about later

Thans

Not applicable

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

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

HeadCount.png

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?

Not applicable

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

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!

Community Browser