Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

challenge

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

1 Solution

Accepted Solutions
Sokkorn
Honored Contributor

Re: challenge

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

24 Replies
sivarajs
Valued Contributor II

Re: challenge

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

Sokkorn
Honored Contributor

Re: challenge

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

Not applicable

Re: challenge

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

Sokkorn
Honored Contributor

Re: challenge

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]

Not applicable

Re: challenge

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.

sivarajs
Valued Contributor II

Re: challenge

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

Not applicable

Re: challenge

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

  1. 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
sivarajs
Valued Contributor II

Re: challenge

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

Not applicable

Re: challenge

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

Community Browser