Re: Getting period of data - Page 2 - Qlik Community

prasad333 · ‎2018-04-25

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

prasad333 · ‎2018-04-25

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

YoussefBelloum · ‎2018-04-25

what is the format of that date field ?

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

prasad333 · ‎2018-04-25

dd/mm/yyyy

YoussefBelloum · ‎2018-04-26

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 · ‎2018-04-26

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 · ‎2018-04-26

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

prasad333 · ‎2018-04-26

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

I want it like this -

YoussefBelloum · ‎2018-04-26

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

prasad333 · ‎2018-04-26

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 · ‎2018-04-26

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