Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

bimartingo
New Contributor

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

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

1 Solution

Accepted Solutions
swarup_malli
Valued Contributor

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

Martino,

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

Regards

Swarup

9 Replies
swarup_malli
Valued Contributor

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

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
New Contributor

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

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
New Contributor

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

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

Not applicable

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

Martingo,

Check out the attachment.

Kiran.

swarup_malli
Valued Contributor

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

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
Valued Contributor

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

Martino,

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

Regards

Swarup

bimartingo
New Contributor

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

Thanks

bimartingo
New Contributor

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

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
New Contributor

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

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

Community Browser