Solved: Re: challenge - Qlik Community

Report Inappropriate Content · ‎2013-07-10

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

Sokkorn · ‎2013-07-11

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

View solution in original post

sivarajs · ‎2013-07-10

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 · ‎2013-07-11

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

Report Inappropriate Content · ‎2013-07-11

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

Sokkorn · ‎2013-07-11

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]

Report Inappropriate Content · ‎2013-07-11

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 · ‎2013-07-11

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

Report Inappropriate Content · ‎2013-07-11

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

sivarajs · ‎2013-07-11

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

Report Inappropriate Content · ‎2013-07-11

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