challenge

[#1]

I have been challenging to work with my dashboard report

1. Orders with in last 30 days
2. Orders between 31- 60 days
3. Orders between 60 - 90 days
4. Orders between 90 - 180 days
5. Orders 180 and beyond

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

[Sokkorn]

Hi,

Try below:

[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

(biff, embedded labels, table is Orders);

[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];

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);

[#3]

when i do that it show up like this :

script error

Unknown statement

[MaxDate]

LOAD Max([OrderDate]) As [MaxDate] Resident [OrderData]

Table not found

DROP TABLES statement

[Sokkorn · Accepted solution]

How about this one

[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
(biff, embedded labels, table is Orders);
[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];

[#5]

try like below

MaxDate:

LOAD
max([OrderDate]) AS MaxDate

RESIDENT OrderDateTable;

Regards-Bika

[#6]

thank you soo much,i have been trying to solve this since three days. i had spend more then 20 hours trying to find the answer for this.i really appreciate ur kind help.