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
YoussefBelloum
Champion
Champion

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 ?

prasad333
Contributor III
Contributor III
Author

Hey,

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

YoussefBelloum
Champion
Champion

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

prasad333
Contributor III
Contributor III
Author

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

YoussefBelloum
Champion
Champion

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)

prasad333
Contributor III
Contributor III
Author

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.

YoussefBelloum
Champion
Champion

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

prasad333
Contributor III
Contributor III
Author

I need the scripting. In the back end.

YoussefBelloum
Champion
Champion

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 ?