25 Replies Latest reply: Apr 27, 2018 6:42 AM by youssef belloum RSS

    Getting period of data

    KharanSU RATH

      Hi all,

      So here the scenario is, I have to get the dates for 24 months of period for a sales report and it should be automated. Suppose I should a time period from 1/4/2017 to 1/4/2018 but when it comes to the 25th month, the start date of 1st month should also be changed, so that I would get results for only 24 months.

       

      Should I go for YTD or anything else?

      And it should be in the scripting, not in front end.

       

      Thanks

       

      KHARANSU

        • Re: Getting period of data
          youssef belloum

          Hi,

           

           

          In your example you have 12 months between 1/4/2017 and 1/4/2018


          so do you want 12 months or 24 months ?

            • Re: Getting period of data
              KharanSU RATH

              Hey,

              sorry for the wrong information. It's for 24 months, from 2016.

                • Re: Getting period of data
                  youssef belloum

                  ok,

                   

                  you want to limit your data to the last 24 months in the Script or in a specific chart using an expression ?

                   

                  the difference:

                   

                  if you limit in the script: in all your app you will only have two years of data.

                   

                  if you limit on a chart using an expression: you will have all the data from the source, the limitation will be applied only on a specific chart. so if you need to analyze data before the last two years, it will be possible

                    • Re: Getting period of data
                      KharanSU RATH

                      Yes, you are right. Can you explain the scripts and expressions for both of these conditions?

                        • Re: Getting period of data
                          youssef belloum

                          1. on the script:

                           

                          you will have to use a WHERE() clause at the end of your LOAD

                           

                          example:

                           

                          LOAD

                          .

                          .

                          FROM...

                          WHERE DATE>= 01/04/201 and DATE<= 01/04/2018

                           

                          we can make these dates dynamic on every load (will see that later)

                           

                           

                          2. on the expression

                           

                          you will have to use SET ANALYIS on your expression

                           

                          example:

                           

                          Sum(     {<Date_field={">=01/04/2016<=01/04/2018"}>}     Measure_field)

                           

                          also here we can make these dates dynamic (will see that later)

                            • Re: Getting period of data
                              KharanSU RATH

                              Thank you but these are hard coded. Once new data comes in, again I have to change it. I have to keep it dynamic, so that whatever the data comes, it will vary between that time.

                                • Re: Getting period of data
                                  youssef belloum

                                  Like what I said above:

                                   

                                  we can make these dates dynamic


                                  you should choose between these two methods described above, and I'll show you how to make it dynamic

                                    • Re: Getting period of data
                                      KharanSU RATH

                                      I need the scripting. In the back end.

                                        • Re: Getting period of data
                                          youssef belloum

                                          Ok, what is the name of your date field ? and how do you load that field in the script ?

                                           

                                          do you use Date() or Date#() function ?

                                           

                                          all this to know if your date field is correctly evaluated as a Date and what is its format ?

                                            • Re: Getting period of data
                                              KharanSU RATH

                                              There I am using "LAUNCH DATE" as the required date field.

                                                • Re: Getting period of data
                                                  youssef belloum

                                                  what is the format of that date field ?

                                                   

                                                  it is: DD/MM/YYYY or MM/DD/YYYY or MM-DD-YYYY or .. ?

                                                    • Re: Getting period of data
                                                      KharanSU RATH

                                                      dd/mm/yyyy

                                                        • Re: Getting period of data
                                                          youssef belloum

                                                          Ok

                                                           

                                                          so to roll back 24 months dynamically and to have this value today: 01/04/2016, you can use this:

                                                           

                                                          =MonthStart(AddYears(Date(today()),-2))

                                                           

                                                          to get the first day of the actual month, you can use this:

                                                           

                                                          MonthStart(today())

                                                           

                                                          on you script, at the end of the load, do this:

                                                           

                                                          TABLE:

                                                          LOAD

                                                          .

                                                          .

                                                          FROM

                                                          WHERE DATE_FIELD >= MonthStart(AddYears(Date(today()),-2)) and DATE_FIELD <= MonthStart(today());

                                                            • Re: Getting period of data
                                                              KharanSU RATH

                                                              WHERE DATE_FIELD >= MonthStart(AddYears(Date(today()),-2)) and DATE_FIELD <= MonthStart(today());

                                                               

                                                              here, in place of AddYears, should I put any values or keep it like that?

                                                                • Re: Getting period of data
                                                                  youssef belloum

                                                                  Like this it is 100% dynamic on each reload, so keep it like that and let me know

                                                                    • Re: Getting period of data
                                                                      KharanSU RATH

                                                                      But when 'am showing it as a FIELD it's showing only "-1" values in the table viewer.

                                                                      I want it like this -

                                                                       

                                                                      Screenshot (18).png

                                                                        • Re: Getting period of data
                                                                          youssef belloum

                                                                          What exactly are you showing as a field to get only "-1" as values ?

                                                                            • Re: Getting period of data
                                                                              KharanSU RATH

                                                                              No, I don't want to show "-1" but when I am putting the script you have suggested I'm getting "-1" but I want to get values like the screenshot I have attached.

                                                                               

                                                                              I am not able to attach the qvf, so copying the part of scripting I have used.

                                                                               

                                                                              Atom_Summary:

                                                                              load* Where not Exists(Project_code, Project);

                                                                              LOAD

                                                                                  Open,

                                                                                  Description,

                                                                                  Name AS Project,

                                                                                  "Starter Project ID",

                                                                                  "Project types",

                                                                                  Region,

                                                                                  ApplyMap('map1', Country) as Country,

                                                                                  Molecule,

                                                                                  Upper(Brand) AS Brand,

                                                                                  "Launch type",

                                                                                  "Pharmaceutical form",

                                                                                  Strengths,

                                                                                  "Manufacturing site",

                                                                                  "NPI 3rd year incremental sales (USD)" as NPI_SALES,

                                                                                  Date("Planned launch date") as Launch_date ,

                                                                                  Year("Planned launch date") as Year,

                                                                                  Month("Planned launch date") as Month,

                                                                                  'Q' & Ceil(Month("Planned launch date") /3) as Quarter,

                                                                                  "Planned launch date" >= MonthStart(AddYears(Date(today()),-2)) and "Planned launch date" <= MonthStart(today()) as Aging,

                                                                                  "Overall Project Status",

                                                                                  "Project Status",

                                                                                  "Stage & gate",

                                                                                  "Next gate to submit",

                                                                                  "Master data readiness",

                                                                                  "SKU Countries"

                                                                              FROM [lib://Project_etl/Into Qlik sense\ATOM SUMMARY.xlsx]

                                                                              (ooxml, embedded labels, header is 1 lines, table is [ATOM SUMMARY])


                                                                              Where match([Project Status],'Launched') and Match([Project types],'GeoEx','LA') and Match([Launch type],'Line Extension','New Indication','New Molecule','Additional Brand')

                                                                              and  not Match([SKU Countries],'BN,MY','MM','MO','KH') and not WildMatch(Description,'*Sample*');

                                                                                • Re: Getting period of data
                                                                                  youssef belloum

                                                                                  you didn't read carefully what I said.. and you didn't mention that you want a field called age which represent the months age..

                                                                                   

                                                                                  from the start, it was about reducing DATA to the last 24 months, and do it on the script.

                                                                                   

                                                                                  I asked you to put the line on the WHERE clause and not inside the LOAD..

                                                                                   

                                                                                  so try this (supposing everything else is working perfectly on your existing load):

                                                                                   

                                                                                  //ADD THIS LINE

                                                                                  //Function to calculate Months Difference in script

                                                                                  SET MonthDiff = Num(((year($2) * 12) + month($2)) - (((year($1) * 12) + month($1))) + 1);

                                                                                   

                                                                                  Atom_Summary:

                                                                                  load* Where not Exists(Project_code, Project);

                                                                                  LOAD

                                                                                      Open,

                                                                                      Description,

                                                                                      Name AS Project,

                                                                                      "Starter Project ID",

                                                                                      "Project types",

                                                                                      Region,

                                                                                      ApplyMap('map1', Country) as Country,

                                                                                      Molecule,

                                                                                      Upper(Brand) AS Brand,

                                                                                      "Launch type",

                                                                                      "Pharmaceutical form",

                                                                                      Strengths,

                                                                                      "Manufacturing site",

                                                                                      "NPI 3rd year incremental sales (USD)" as NPI_SALES,

                                                                                      Date("Planned launch date") as Launch_date ,

                                                                                      Year("Planned launch date") as Year,

                                                                                      Month("Planned launch date") as Month,

                                                                                      'Q' & Ceil(Month("Planned launch date") /3) as Quarter,

                                                                                   

                                                                                       ($(MonthDiff("Planned launch date", today())) AS Aging,


                                                                                      "Overall Project Status",

                                                                                      "Project Status",

                                                                                      "Stage & gate",

                                                                                      "Next gate to submit",

                                                                                      "Master data readiness",

                                                                                      "SKU Countries"

                                                                                  FROM [lib://Project_etl/Into Qlik sense\ATOM SUMMARY.xlsx]

                                                                                  (ooxml, embedded labels, header is 1 lines, table is [ATOM SUMMARY])


                                                                                  Where match([Project Status],'Launched') and Match([Project types],'GeoEx','LA') and Match([Launch type],'Line Extension','New Indication','New Molecule','Additional Brand')

                                                                                  and  not Match([SKU Countries],'BN,MY','MM','MO','KH') and not WildMatch(Description,'*Sample*')

                                                                                  AND "Planned launch date" >= MonthStart(AddYears(Date(today()),-2)) and "Planned launch date" <= MonthStart(today()) ;