Compare the month in This year vs. same month in last year

[bimartingo]

Hi,

i'm just learning, so excuse me if my question is somehow naif.

I have a table with orders (order_ID, order_date , order_value) and want to build a graph containing the monthsname(order_date) as dimension an two expressions of kind sum(order_value) :

expr1 : sum of the order_value for the month given

expr2 : sum of the order_value for the  same month but of the year before.

Example:

Data:

Id           Date            Value        monthname(Date)

232323   4/15/2003     150.0        'apr 2003'

324455   4/05/2004     200.0        'apr 2004'

expr1 : Sum Order _Value for 'apr 2004' = 200

expr2 : Sum Order_Value for 'apr 2003' = 150

Final set  (for Graph)

monthname               expr1      expr2

'apr 2003'                   150           -           

'apr 2004'                   200        150

[swarup_malli]

Martino,

   You'll first have to create a master calender.

Tthen you'll have to create a variable, then pass the date value to the variable. I hope this was helpful.

Regards

Swarup

[bimartingo]

Swarup,

first, thank for your help.

i had created a calendar table using the data of my orders table, like this:

    DimCalendar:

    load distinct Year(order_date) as Year_Order,Month(order_date) as Month_Order

     , (Year(order_date)*100 + Month(order_date)) as IdMonthYear

     , Year(YearStart(order_date,-1)) as Year_Before_Pedido

     , order_date

     , monthname(order_date,-12) as YearMonth_Before_order

     , monthname(order_date,0) as YearMonth_order

    Resident Order;

And how should i use this variable to get the desired results?

Regards

Martingo

[bimartingo]

Another possible solution would be create a table in crosstab form like this:

table with 3 columns

for each date

column 1 : date

column 2 : sum of all order_value where order_date = date

column 3 : sum of all order_value where order_date = date one year before

what do you think?

Regards

biMartingo

[#5]

Martingo,

Check out the attachment.

Kiran.

[swarup_malli]

Martino,

I'm attaching a sample application.On the first tab ie dashboard you'll find a bar chart(Total sales over years ) where , this years and last years comparsion is being done.

The application I'm attaching is very useful for ppl who r new to Qlikview. U'll get a good picture by playing with it.

First create a master table, (U'll find the code for that in the script editor).  For creating variable, go to document properties (Ctrl+Alt+D), u'll find variables tab, u can createa and pass values there.

Regards

Swarup

[swarup_malli · Accepted solution]

Martino,

Try this approach( I have attached a pdf ..check out the page no 18 ..very helpful)

Regards

Swarup

[bimartingo]

Thanks

[bimartingo]

Thank you all.

I'm now working on a Project envolving SAS, so I'll have to interrupt my studies on Qlikwiew.

Regards

Martingo

[bimartingo]

Yes, was very helpful.

using a calendar table with date, month,year and "set expression"

using dimension = month

and series (expressions)

current year : Sum ( value)

year before : Sum ({1<year = {$(=year-1)}>}   value)

year before : sum value  for all data where year = (selected year - 1)

Thanks

BIMartingo