topic Last year figures in QlikView

Last year figures

Mon, 27 Feb 2012 06:45:06 GMT

Hi,

I use an expression to get current year cumulative figures which perfectly works :

sum ({$<Year = {$(=max(Year))}, Month = {"<=$(=max({<Year={$(=max(Year))}>} Month))"}>} Sales)

I'd like to display last year same figures. The idea is when i select a month (april 2011 for exemple), I'd like to see sales from january to april 2011 (expression above) and sales from january to april 2010 and I can't figure out how to get this.

Many thanks for yor help.

Franck

Last year figures

Mon, 27 Feb 2012 06:55:11 GMT

You can use following

sum ({$<Year = {$(=max(Year)-1)}, Month = {"<=$(=max({<Year={$(=max(Year))}>} Month))"}>} Sales)

This will give you details of previous year.

Is it ok?

Last year figures

Mon, 27 Feb 2012 07:22:26 GMT

Thanks for your answer.

This expression gives me the whole last year figures and what I'd like to get is the sales for the period depending on the month selection. For exemple, if i select april 2011, i' want to obtein the figures from january to april 2011.

Regards.

Franck

Last year figures

jagan — Mon, 27 Feb 2012 08:03:03 GMT

Hi,

Try the expression

sum ({$<Year = {$(=max(Year)-1)}, Month = {"<=$(=max(Month))"}>} Sales)

This works if your month is in Numerice format.

Regards,

Jagan.

Last year figures

Mon, 27 Feb 2012 08:29:06 GMT

Hi Jagan, thank you for your answer.

Unfortunately, the format of the month is MMM-YYY and your expression still gives me the whole last year sum of sales.

Regards,

Franck

Last year figures

CELAMBARASAN — Mon, 27 Feb 2012 08:34:11 GMT

Hi,

If you have a date field then check with this

=sum ({$<Year = {$(=max(Year)-1)},DataField={">=MonthStart(Max(DateField),-12)"}, Month=}>} Sales)

Hope it helps

Celambarasan

Last year figures

jagan — Mon, 27 Feb 2012 08:35:24 GMT

Hi,

Arrive the month in Numeric format, it would be easier for you to calculate this type fo calculations.

LOAD

Month(Date(Date#(MonthFieldName, 'MMM-YYYY'), 'MM/DD/YYYY')) AS Month

FROM DataSource;

Now use the above expression.

Hope this helps you.

Regards,

Jagan.

Last year figures

Mon, 27 Feb 2012 08:51:03 GMT

Hi,

You can probably use the Month field instead of Month-Year to get the result. If you do not already have a month field (Jan,Feb....), you can create the month field by using month(YourDateFieldName) as Month in the script. Then use the expression that Jagan posted.

As far as month names are concerned, you could use an inline mapping table for mapping months with their corresponding names as follows....

Months_Map:

LOAD * INLINE [

Month, M_Name

1, Jan

2, Feb

3, Mar

4, Apr

5, May

6, Jun

7, Jul

8, Aug

9, Sep

10, Oct

11, Nov

12, Dec

];

Hope this helps.

Regards,

-Khaled.

Last year figures

Mon, 27 Feb 2012 15:16:43 GMT

Thanks for your answers, but it still doesn't work. The big problem is that I can't do nothing with the script because I work with VPN connection and I can't access to the database.

Thanks anyway

Re: Last year figures

Mon, 27 Feb 2012 16:01:41 GMT

By using partial reload, I could insert a numeric month field. Here is the code :

[Calendar]:

ADD LOAD *,

Date(DAT_ADMIN) AS CalendarDate,

Day(DAT_ADMIN) AS CalendarDay,

WeekDay(DAT_ADMIN) AS CalendarDayName,

Week(DAT_ADMIN) AS Week,

Month(DAT_ADMIN) AS CalendarMonth,

'Q' & Ceil(Month(DAT_ADMIN)/3) AS Quarter,

Year(DAT_ADMIN) AS Year,

WeekName(DAT_ADMIN) as CalendarWeekNumberAndYear,

MonthName(DAT_ADMIN) as Month,

QuarterName(DAT_ADMIN) as CalendarQuarterMonthsAndYear,

DayStart(DAT_ADMIN) as CalendarDayStart,

WeekStart(DAT_ADMIN) as CalendarWeekStart,

MonthStart(DAT_ADMIN) as CalendarMonthStart,

QuarterStart(DAT_ADMIN) as CalendarQuarterStart,

YearStart(DAT_ADMIN) as CalendarYearStart,

DayEnd(DAT_ADMIN) as CalendarDayEnd,

WeekEnd(DAT_ADMIN) as CalendarWeekEnd,

MonthEnd(DAT_ADMIN) as CalendarMonthEnd,

QuarterEnd(DAT_ADMIN) as CalendarQuarterEnd,

YearEnd(DAT_ADMIN) as CalendarYearEnd,

'Q' & Ceil(Month(DAT_ADMIN)/3) & '/' & Year(DAT_ADMIN) AS CalendarQuarterAndYear,

Num(Month(DAT_ADMIN)) AS MonthNo,

Year(DAT_ADMIN) & '/' & 'Q' & Ceil(Month(DAT_ADMIN)/3) AS CalendarYearAndQuarter;

ADD LOAD date(fieldvalue('DAT_ADMIN',recno())) as DAT_ADMIN

AUTOGENERATE fieldvaluecount('DAT_ADMIN')

;

Any idea ?

Many thanks in advance.

Franck

Last year figures

CELAMBARASAN — Tue, 28 Feb 2012 04:37:03 GMT

Hi,

Try this

=sum ({$<Year = {$(=max(Year)-1)},MonthNo={">$(=Max(MonthNo))"}, CalendarMonth=}>} Sales)

Hope it helps

Celambarasan

Last year figures

Tue, 28 Feb 2012 06:39:39 GMT

Jagan's expression works with a numeric month field. Thanks for your help guys !

Re: Last year figures

jagan — Tue, 28 Feb 2012 06:45:55 GMT

Hi,

Close this discussion by selecting the correct answer, if it solves your issue. It helps others to find the solution for same scenario.

Regards,

Jagan.