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

[#2]

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]

[Sokkorn]

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

[#4]

Thank you, is there anyway i can get

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

between the data or something else.

[Sokkorn]

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

[#6]

1. Since the data  is only until 1999. You don't have to show the latest but the use the current date as the last day orders received in the database.

[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

[Anonymous]

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

[#9]

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

[Sokkorn]

Hi Sir,

Can you share your script here? So that we can figure it out together.

Regards,

Sokkorn

[#11]

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