Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
prasad333
Contributor III
Contributor III

Getting period of data

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

25 Replies
prasad333
Contributor III
Contributor III
Author

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

YoussefBelloum
Champion
Champion

what is the format of that date field ?

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

prasad333
Contributor III
Contributor III
Author

dd/mm/yyyy

YoussefBelloum
Champion
Champion

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

prasad333
Contributor III
Contributor III
Author

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?

YoussefBelloum
Champion
Champion

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

prasad333
Contributor III
Contributor III
Author

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

YoussefBelloum
Champion
Champion

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

prasad333
Contributor III
Contributor III
Author

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

YoussefBelloum
Champion
Champion

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