topic Re: Getting period of data in App Development

Getting period of data

prasad333 — Wed, 25 Apr 2018 05:14:40 GMT

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

YoussefBelloum — Wed, 25 Apr 2018 08:40:53 GMT

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

prasad333 — Wed, 25 Apr 2018 08:48:50 GMT

Hey,

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

Re: Getting period of data

YoussefBelloum — Wed, 25 Apr 2018 08:53:47 GMT

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

prasad333 — Wed, 25 Apr 2018 08:58:05 GMT

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

Re: Getting period of data

YoussefBelloum — Wed, 25 Apr 2018 09:05:58 GMT

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

prasad333 — Wed, 25 Apr 2018 09:13:17 GMT

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

YoussefBelloum — Wed, 25 Apr 2018 09:39:59 GMT

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

prasad333 — Wed, 25 Apr 2018 10:29:35 GMT

I need the scripting. In the back end.

Re: Getting period of data

YoussefBelloum — Wed, 25 Apr 2018 11:35:53 GMT

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

prasad333 — Wed, 25 Apr 2018 12:32:32 GMT

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

Re: Getting period of data

YoussefBelloum — Wed, 25 Apr 2018 12:43:20 GMT

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

prasad333 — Wed, 25 Apr 2018 18:27:55 GMT

dd/mm/yyyy

Re: Getting period of data

YoussefBelloum — Thu, 26 Apr 2018 07:33:16 GMT

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

prasad333 — Thu, 26 Apr 2018 09:28:11 GMT

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

YoussefBelloum — Thu, 26 Apr 2018 09:32:27 GMT

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

Re: Getting period of data

prasad333 — Thu, 26 Apr 2018 10:12:16 GMT

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

I want it like this -

Re: Getting period of data

YoussefBelloum — Thu, 26 Apr 2018 12:02:21 GMT

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

Re: Getting period of data

prasad333 — Thu, 26 Apr 2018 12:30:15 GMT

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

YoussefBelloum — Thu, 26 Apr 2018 13:18:53 GMT

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