Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Martino,
Try this approach( I have attached a pdf ..check out the page no 18 ..very helpful)
Regards
Swarup
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
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
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
Martingo,
Check out the attachment.
Kiran.
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
Martino,
Try this approach( I have attached a pdf ..check out the page no 18 ..very helpful)
Regards
Swarup
Thanks
Thank you all.
I'm now working on a Project envolving SAS, so I'll have to interrupt my studies on Qlikwiew.
Regards
Martingo
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