Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have been challenging to work with my dashboard report
right now my script looks like this this
OrderID,
CustomerID,
EmployeeID,
OrderDate,
Year(OrderDate) as Year,
Month(OrderDate) as Month,
Week(OrderDate) as Week,
RequiredDate,
ShippedDate,
ShipVia,
Freight,
ShipName,
ShipAddress,
ShipCity,
ShipRegion,
ShipPostalCode,
ShipCountry
please donot write something like this because i am new to qlikview.specific script will be appreciated.
thank you
where exactly do i have to put here or ..........
CustomerID,
EmployeeID,
OrderDate,
Year(OrderDate) as Year,
Month(OrderDate) as Month,
Week(OrderDate) as Week,
RequiredDate,
ShippedDate,
ShipVia,
Freight,
ShipName,
ShipAddress,
ShipCity,
ShipRegion,
ShipPostalCode,
ShipCountry,
If(Today()-OrderDate<31, '0-30',
If(Today()-OrderDate<61, '30-61',
If(Today()-OrderDate<91, '60-90',
If(Today()-OrderDate<181, '90-180',
If(Today()-OrderDate>180, 'Over 181'
))))) AS [OrderAging]
Hi,
That correct script. And the result return only 'Over 181' is correct too. Why? You said "OrderDate" have in year 1996,1997 and 1998. The script above will calculate; Ex. '11-Jul-2013' - '01-Jan-1996' and return value > 181.
Regards,
Sokkorn
Thank you, is there anyway i can get
between the data or something else.
Ok,
First you need to define a specific date for calculate aging. I want "Last 30 days" order date. Is it "Last 30 days" from today or other date?
Pleaser answer above question then we can move next; otherwise no solution.
Rgds,
Sokkorn
Hi,
Let try this:
[OrderData]:
LOAD
OrderID,
CustomerID,
EmployeeID,
OrderDate,
Year(OrderDate) as Year,
Month(OrderDate) as Month,
Week(OrderDate) as Week,
RequiredDate,
ShippedDate,
ShipVia,
Freight,
ShipName,
ShipAddress,
ShipCity,
ShipRegion,
ShipPostalCode,
ShipCountry
From...
[MaxDate]:
LOAD Max([OrderDate]) As [MaxDate] Resident [OrderData];
Let varMaxDate = Peek('MaxDate',0,'MaxDate');
Drop Table [MaxDate]
[OrderData2]:
LOAD
*,
If($(varMaxDate)-[OrderDate]<31, '0-30',
If($(varMaxDate)-[OrderDate]<61, '30-61',
If($(varMaxDate)-[OrderDate]<91, '60-90',
If($(varMaxDate)-[OrderDate]<181, '90-180',
If($(varMaxDate)-[OrderDate]>180, 'Over 181'
)))) AS [OrderAging]
Resident [OrderData];
Drop Table [OrderData];
Regards,
Sokkorn
Look up the INTERVALMATCH functionality.
What this allows you to do is set up groups (I call them aging buckets) of start and end age ranges, something like:
Buckets:
LOAD * INLINE [
Bucket, BucketStart, BucketEnd
<30 days, 0, 29
<60 days, 30, 59
];
You then need to calculate the number of days that it has been since each order was placed, adding this line to your Orders Load statement:
Today()-OrderDate AS OrderDays
Then you can run the interval match command, passing in the start and end date:
INTERVALMATCH (OrderDays) LOAD BucketStart, BucketEnd RESIDENT Buckets;
This will create a synthetic table linking your OrderDays field to the different bucket age ranges that these days apply to.
You can then add the Bucket field as the dimension to your chart, and sum up the OrderDays on your expression.
Jonathan
Hi, Sokkorn, thanks for time and kind and considerable help.i copy ur script and paste it,still syntax error coming.i dont know whats going on.I really appreciate ur help
Hi Sir,
Can you share your script here? So that we can figure it out together.
Regards,
Sokkorn
this is my overall script
[OrderData]:
LOAD
OrderID,
CustomerID,
EmployeeID,
OrderDate,
Year(OrderDate) as Year,
Month(OrderDate) as Month,
Week(OrderDate) as Week,
RequiredDate,
ShippedDate,
ShipVia,
Freight,
ShipName,
ShipAddress,
ShipCity,
ShipRegion,
ShipPostalCode,
ShipCountry,
From...
[MaxDate]
LOAD Max([OrderDate]) As [MaxDate] Resident [OrderData];
Let varMaxDate = Peek('MaxDate',0,'MaxDate');
Drop Table [MaxDate]
[OrderData2]:
LOAD
*,
If(Today()-$(varMaxDate)<31, '0-30',
If(Today()-$(varMaxDate)<61, '30-61',
If(Today()-$(varMaxDate)<91, '60-90',
If(Today()-$(varMaxDate)<181, '90-180',
If(Today()-$(varMaxDate)>180, 'Over 181'
)))) AS [OrderAging]
Resident [OrderData];
Drop Table [OrderData];
FROM
(biff, embedded labels, table is Orders);
LOAD OrderID,
ProductID,
UnitPrice,
Quantity,
Discount,
(UnitPrice*Quantity)-Discount as Sales
FROM
(biff, embedded labels, table is Order_Details);
LOAD ProductID as ObjectID,
ProductName,
SupplierID,
CategoryID,
QuantityPerUnit,
UnitPrice,
UnitsInStock,
UnitsOnOrder,
ReorderLevel,
Discontinued
FROM
(biff, embedded labels, table is Products);
LOAD CustomerID,
CompanyName,
ContactName,
ContactTitle,
Address,
City,
Region,
PostalCode,
Country,
Phone,
Fax
FROM
(biff, embedded labels, table is Customers);