topic help in expression in QlikView

help in expression

ali_hijazi — Wed, 12 Nov 2014 14:28:32 GMT

got a pivot table with Month as Dimension

my expression is simple sum(Sales)

however for each month I want to display a value representing the sales for previous month

Message was edited by: Ali Hijazi

Re: help in expression

jpenuliar — Wed, 12 Nov 2014 14:33:34 GMT

maybe something like :

=COUNT({<Field_Month={$(=Month(AddMonths(Date(Today()),0)))}>}COUNTER)

addMonths() should be the key

Re: help in expression

ali_hijazi — Wed, 12 Nov 2014 14:37:16 GMT

no cause there might be more than one month displayed

this works if one month is selected

Re: help in expression

maleksafa — Wed, 12 Nov 2014 14:38:28 GMT

can you upload a sample document.?

Re: help in expression

agomes1971 — Wed, 12 Nov 2014 14:51:52 GMT

Hi,

if you have for example select the present year - 2014.

You can do like this:

Sum({$<Year={">$(=Year-5)<=$(=Year-1)"}>}Sales)/1000.

Now think like they were months...

Hope it helps

André Gomes

Re: help in expression

Wed, 12 Nov 2014 15:08:58 GMT

Hi,

Try this

sum({<Month={$(=Month(AddMonths(Date(Today()),-1)))}>} Sales)

Re: help in expression

ali_hijazi — Wed, 12 Nov 2014 15:18:29 GMT

check attached sample data in excel

Re: help in expression

Gysbert_Wassenaar — Wed, 12 Nov 2014 15:29:23 GMT

See attached qvw.

Re: help in expression

MarcoWedel — Wed, 12 Nov 2014 15:35:21 GMT

use before(sum(Sales)) or above(sum(Sales))

(supposed there are values for each month in your model)

hope this helps

regards

Marco

Re: help in expression

ali_hijazi — Wed, 12 Nov 2014 15:35:49 GMT

but I need the dimension to be month jan, feb,...

Re: help in expression

Gysbert_Wassenaar — Wed, 12 Nov 2014 17:33:12 GMT

Then create an AsOf table with those values. The concept will be the same. Good luck.

Re: Re: help in expression

MarcoWedel — Wed, 12 Nov 2014 23:04:28 GMT

Hi,

one solution could be:

tabSales:
LOAD *
FROM [http://community.qlik.com/servlet/JiveServlet/download/654995-137136/13MonthsTrend.xlsx] (ooxml, embedded labels, table is Sheet1);

tabCalendar:
LOAD *,
    Day(Date) as Day,
    WeekDay(Date) as WeekDay,
    Week(Date) as Week,
    WeekName(Date) as WeekName,
    Month(Date) as Month,
    MonthName(Date) as MonthName,
    Ceil(Month(Date)/3) as Quarter,
    QuarterName(Date) as QuarterName,
    Year(Date) as Year,
    WeekYear(Date) as WeekYear;   
LOAD Date(MakeDate(2009)+IterNo()-1) as Date
AutoGenerate 1
While MakeDate(2009)+IterNo()-1<=Today();

hope this helps

regards

Marco