topic Re: Set Future Months Value in QlikView

Set Future Months Value

Wed, 29 Jun 2016 14:48:16 GMT

Hi Guys,

I've a table in below format, in which i've data available till Apr 2016 for each employee.

Employee	Month	Headcount
Ed	Jan-2016	1
Vincent	Jan-2016	1
Sam	Jan-2016	1
Ed	Feb-2016	1
Vincent	Feb-2016	1
Sam	Mar-2016	1
Ed	Mar-2016	1
Vincent	Mar-2016	1
Sam	Mar-2016	1
Ed	Apr-2016	1
Vincent	Apr-2016	1
Sam	Apr-2016	1

In frond end i want to set all the future months headcount as the headcounts for latest month available in the data. Something like below.

	Jan	Feb	Mar	Apr	May	June	July	Aug	Sep	Oct	Nov	Dec
Ed	1	1	1	1	1	1	1	1	1	1	1	1
Vincent	1	1	1	1	1	1	1	1	1	1	1	1
Sam	1	1	1	1	1	1	1	1	1	1	1	1

Is it possible to achieve the same using a set analysis expression or is there any other way to achieve this? Please help

Thanks in advance.

Re: Set Future Months Value

settu_periasamy — Wed, 29 Jun 2016 18:16:18 GMT

Hi,

I think you need to look on "ValueList". I just created a sample based on your sample.

Dimension : Employee

Calculated dimension :

=ValueList('Jan 2016','Feb 2016','Mar 2016','Apr 2016','May 2016','Jun 2016')

(I just created for 6 months, you can add more)

Expression:

Pick(Match(ValueList('Jan 2016','Feb 2016','Mar 2016','Apr 2016','May 2016','Jun 2016'),'Jan 2016','Feb 2016','Mar 2016','Apr 2016','May 2016','Jun 2016'),
sum({<Month={'Jan 2016'}>}Headcount),
sum({<Month={'Feb 2016'}>}Headcount),
sum({<Month={'Mar 2016'}>}Headcount),
sum({<Month={'Apr 2016'}>}Headcount),
sum({<Month={'$(=MonthName(max(Month)))'}>}Headcount),
sum({<Month={'$(=MonthName(max(Month)))'}>}Headcount))