topic Re: Current year vs Previous year in QlikView

Current year vs Previous year

Fri, 13 Feb 2015 13:59:44 GMT

I have these fields in my database, PayRollYear and PayRollMonth and EMPNO.

I want to create a combo chart, that shows the count of employees in the present year vs the previous year. Present year would be a bar chart, and previous year would be a line chart, month being the dimension. Any ideas????

Re: Current year vs Previous year

Michiel_QV_Fan — Fri, 13 Feb 2015 14:02:37 GMT

Create 2 expressions for the count:

sum({<PayRollYear = {'2015'}>}EMPNO)

sum({<PayRollYear = {'2014'}>}EMPNO)

add month to the bar and set the 2 expressions as desired to line or bar.

also consider to use max(year) and max(year)-1 to make the year dependent on your year selection

Re: Current year vs Previous year

Fri, 13 Feb 2015 14:07:09 GMT

This works fine, but if I use, year and year-1, it does not work. Gives the same value for both. MichielvandeGoor

Re: Current year vs Previous year

its_anandrjs — Fri, 13 Feb 2015 14:09:57 GMT

Hi,

Try to use with Max and Max - 1

For Current Year

sum({<PayRollYear = {'$(Max(PayRollYear))'}>}EMPNO)

For Previous Year

sum({<PayRollYear = {'$(Max(PayRollYear)-1)'}>}EMPNO)

sum({<PayRollYear = {'$(Year(Date(Max(PayRollYear)-1)))'}>}EMPNO)

Regards

Anand

Re: Current year vs Previous year

Anonymous — Fri, 13 Feb 2015 14:10:15 GMT

Hi,

Create a graph with dimension Month and two expressions:

First expression in bar chart:

count(distinct {<PayRollYear ={$(=Max(PayRollYear))}>}EMPNO)

Second expression in line chart:

count(distinct {<PayRollYear ={$(=(Max(PayRollYear)-1))}>}EMPNO)

Re: Current year vs Previous year

Michiel_QV_Fan — Fri, 13 Feb 2015 14:12:45 GMT

You need to eliminate the year selection if you use set analysis> Just add year = to your set analysis

sum({<PayRollYear = "$(=Max(PayRollYear ))"}, PayRollYear =>}EMPNO)

sum({<PayRollYear = = {"$(=Max(PayRollYear ) -1)"}, PayRollYear =>}EMPNO)

Re: Current year vs Previous year

Fri, 13 Feb 2015 14:12:46 GMT

Thanks a lot! Much appreciated.

Re: Current year vs Previous year

Fri, 13 Feb 2015 14:13:06 GMT

Thanks a ton! It works!

Re: Current year vs Previous year

Michiel_QV_Fan — Fri, 13 Feb 2015 14:16:32 GMT

Please give points to express your thanks

Re: Current year vs Previous year

Anonymous — Fri, 13 Feb 2015 14:21:29 GMT

that's great 🙂

please mark the discussion as solved if the question is solved for you