topic Re: challenge in QlikView

challenge

Thu, 11 Jul 2013 02:19:59 GMT

I have been challenging to work with my dashboard report

Orders with in last 30 days
Orders between 31- 60 days
Orders between 60 - 90 days
Orders between 90 - 180 days
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

Re: challenge

sivarajs — Thu, 11 Jul 2013 03:47:57 GMT

Try with your script

if((today()-OrderDate)<=30,'In last 30 days',

if((today()-OrderDate)>30 and (today()-OrderDate)<=60,'Between 31- 60 days',

if((today()-OrderDate)>60 and (today()-OrderDate)<=90,'Between 61- 90 days',

if((today()-OrderDate)>90 and (today()-OrderDate)<=180,'Between 91-180 days',

if((today()-OrderDate)>180,'Orders 180 and beyond')))))

Re: challenge

Sokkorn — Thu, 11 Jul 2013 04:58:06 GMT

Hi,

You can try this:

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],

Regards,

Sokkorn

Re: challenge

Thu, 11 Jul 2013 04:58:09 GMT

Thanks sivaraj, but still seems like something wrong, can u tell me where exactly to put this script in my script?

Re: challenge

Sokkorn — Thu, 11 Jul 2013 05:02:11 GMT

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,
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]

Re: challenge

Thu, 11 Jul 2013 05:16:17 GMT

thanks sakkorn, but i get only report after 180 days.i didnot get anything from 0-30 and so on i only get 180 and beyond.

Re: challenge

sivarajs — Thu, 11 Jul 2013 05:39:06 GMT

Check your latest Orderdate

Take a listbox ,add Orderdate and sort by descending then you can find latest date.

Probably you may not have recent datas.If recent date is there then check your expression syntax

Re: challenge

Thu, 11 Jul 2013 05:51:55 GMT

i have the data (orderdate)from 1996,97 and 1998. i wanna

Create a Dashboard with any relevant chart (bar, pie, straight table, or anything else) which show the Orders received by the company and show the below Order Range:
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

Re: challenge

sivarajs — Thu, 11 Jul 2013 05:58:52 GMT

Seems like you dont have recent datas, obiviously age of order date will falls under Orders 180 and beyond category.

Re: challenge

Thu, 11 Jul 2013 06:02:05 GMT

Ya i donot have recent data, what should i make a change in my script to make it happen?

Re: challenge

Thu, 11 Jul 2013 06:19:12 GMT

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]

Re: challenge

Sokkorn — Thu, 11 Jul 2013 06:23:49 GMT

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

Re: challenge

Thu, 11 Jul 2013 06:30:05 GMT

Thank you, is there anyway i can get

Orders with in last 30 days
Orders between 31- 60 days
Orders between 60 - 90 days
Orders between 90 - 180 days
Orders 180 and beyond

between the data or something else.

Re: challenge

Sokkorn — Thu, 11 Jul 2013 06:37:07 GMT

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

Re: challenge

Thu, 11 Jul 2013 06:41:01 GMT

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.

Re: challenge

Sokkorn — Thu, 11 Jul 2013 06:51:43 GMT

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

Re: challenge

Anonymous — Thu, 11 Jul 2013 06:51:48 GMT

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

Re: challenge

Thu, 11 Jul 2013 07:21:06 GMT

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

Re: challenge

Sokkorn — Thu, 11 Jul 2013 07:23:13 GMT

Hi Sir,

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

Regards,

Sokkorn

Re: challenge

Thu, 11 Jul 2013 07:31:47 GMT

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