Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

vovan4ik
Valued Contributor II

Show same month last year and last 5 months current year

Hi guys,

Could you help me? I need to build a chart, where should months - the same month of last year and current 5 months.(dinamically)

Other words:

it should be in one chart Aug 2012, April 2013, May 2013, June 2013, July 2013, August 2013.

Thanks,

1 Solution

Accepted Solutions
carlos_reyes_qv
Valued Contributor

Re: Re: Show same month last year and last 5 months current year

My formula works for me, see the attached file.

11 Replies
v_iyyappan
Valued Contributor

Re: Show same month last year and last 5 months current year

Hi,

Script

Num(Datefield) AS DateNum,

first you set current date like selectdate =Max(DateNum)

You need to calculate the six expression in charts, that is

Last year same month:

=(Sum({<Year=, DateBilledNum={'>=$(=(Num(MonthStart(AddYears(SelectedDate,-1))))<=$(=(Num(AddYears((SelectedDate),-1))))'}>}sumfield)

Previous Month of current year

=(Sum({<Year=, DateBilledNum={'>=$(=(Num(MonthStart(SelectedDate,-1)))<=$(=(Num(MonthEnd((SelectedDate),-1)))'}>}sumfield)

then you will reduce the -2,-3,-4,-5 then you will get the previous 4 months .

you will change the expression label also.

Regards,

carlos_reyes_qv
Valued Contributor

Re: Show same month last year and last 5 months current year

It depends on your date field but if you want to use the MonthYear field (assuming that you used the MonthName() function to create it) you were describing and if you want to use only one expression I'd do the next:

SUM (  { $ < MonthYear={ " >= $(#=MonthName(Max(MonthYear),-4) <= $(#=MonthName(Max(MonthYear))) " , "$(=MonthName(Max(MonthYear),-12))" }, Year=, Month= > } YourField )

Hope it helps!

Re: Show same month last year and last 5 months current year

See the attached one

vovan4ik
Valued Contributor II

Re: Show same month last year and last 5 months current year

Something wrong with this expression

Count({<MYear={"$(='>=' & MonthName(Max(MYear),-4) & '<='& MonthName(Max(MYear))"}>} Var1)

vovan4ik
Valued Contributor II

Re: Show same month last year and last 5 months current year

Something wrong with this expression

Count({<MYear={"$(='>=' & MonthName(Max(MYear),-4) & '<='& MonthName(Max(MYear))"}>} Var1)

vovan4ik
Valued Contributor II

Re: Show same month last year and last 5 months current year

The expression works, but  I can't get condinion more than last 4-5 months.

Count({<MYear={">=$(=MonthName(max(MYear),-4)"}>}Var1)

v_iyyappan
Valued Contributor

Re: Show same month last year and last 5 months current year

Hi,

Try like this

= Count({<MYear=,MYear={'>=$(=MonthName(Max(MYear),-4))<=$(=MonthName(Max(MYear))"}>} Var1)

Regards,

vovan4ik
Valued Contributor II

Re: Show same month last year and last 5 months current year

It's Ok, if I'll use only >=than last 4 months. (from last 4 months up to now).

I can'y add condition to this expression. If I use

Count({<MYear={'$(=MonthName(Max(MYear),-4))'}>}[Var1])   -----WORKS

More than 4 months -DOESN'T WORK

Count({<MYear={">=$(=MonthName(Max(MYear),-4))"}>}[Var1])

carlos_reyes_qv
Valued Contributor

Re: Re: Show same month last year and last 5 months current year

My formula works for me, see the attached file.

Community Browser