24 Replies Latest reply: Jul 11, 2013 3:59 AM by som niroula RSS

    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

        • Re: challenge
          sivaraj seeman

          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 Cheav

            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
                Sokkorn Cheav
                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

                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 Cheav

                    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

                        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.

                          • Re: challenge
                            Sokkorn Cheav

                            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
                                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.
                                  • Re: challenge
                                    Sokkorn Cheav

                                    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

                                        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 Cheav

                                            Hi Sir,

                                             

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

                                             

                                            Regards,

                                            Sokkorn

                                              • Re: challenge

                                                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

                                                [C:\qlikview projects\Northwind Data\Orders.xls]

                                                (biff, embedded labels, table is Orders);

                                                 

                                                 

                                                LOAD OrderID,

                                                     ProductID,

                                                     UnitPrice,

                                                     Quantity,

                                                     Discount,

                                                    (UnitPrice*Quantity)-Discount as Sales

                                                FROM

                                                [C:\qlikview projects\Northwind Data\Order Details.xls]

                                                (biff, embedded labels, table is Order_Details);

                                                 

                                                 

                                                LOAD ProductID as ObjectID,

                                                     ProductName,

                                                     SupplierID,

                                                     CategoryID,

                                                     QuantityPerUnit,

                                                     UnitPrice,

                                                     UnitsInStock,

                                                     UnitsOnOrder,

                                                     ReorderLevel,

                                                     Discontinued

                                                FROM

                                                [C:\qlikview projects\Northwind Data\Products.xls]

                                                (biff, embedded labels, table is Products);

                                                 

                                                 

                                                LOAD CustomerID,

                                                     CompanyName,

                                                     ContactName,

                                                     ContactTitle,

                                                     Address,

                                                     City,

                                                     Region,

                                                     PostalCode,

                                                     Country,

                                                     Phone,

                                                     Fax

                                                FROM

                                                [C:\qlikview projects\Northwind Data\Customers.xls]

                                                (biff, embedded labels, table is Customers);

                                                  • Re: challenge
                                                    Sokkorn Cheav

                                                    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

                                                    [C:\qlikview projects\Northwind Data\Orders.xls]

                                                    (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

                                                    [C:\qlikview projects\Northwind Data\Order Details.xls]

                                                    (biff, embedded labels, table is Order_Details);

                                                     

                                                     

                                                    LOAD ProductID as ObjectID,

                                                         ProductName,

                                                         SupplierID,

                                                         CategoryID,

                                                         QuantityPerUnit,

                                                         UnitPrice,

                                                         UnitsInStock,

                                                         UnitsOnOrder,

                                                         ReorderLevel,

                                                         Discontinued

                                                    FROM

                                                    [C:\qlikview projects\Northwind Data\Products.xls]

                                                    (biff, embedded labels, table is Products);

                                                     

                                                     

                                                    LOAD CustomerID,

                                                         CompanyName,

                                                         ContactName,

                                                         ContactTitle,

                                                         Address,

                                                         City,

                                                         Region,

                                                         PostalCode,

                                                         Country,

                                                         Phone,

                                                         Fax

                                                    FROM

                                                    [C:\qlikview projects\Northwind Data\Customers.xls]

                                                    (biff, embedded labels, table is Customers);

                                                      • Re: challenge

                                                        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

                                                          • Re: challenge
                                                            Sokkorn Cheav

                                                            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
                                                            [C:\qlikview projects\Northwind Data\Orders.xls]
                                                            (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];
                                                            
                                                            • Re: challenge

                                                              try like below

                                                               

                                                              MaxDate:

                                                              LOAD
                                                              max([OrderDate]) AS MaxDate

                                                               

                                                              RESIDENT OrderDateTable;

                                                               

                                                              Regards-Bika

                                        • Re: challenge
                                          Jonathan Brough

                                          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