Getting period of data

[prasad333]

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

[YoussefBelloum]

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]

Hey,

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

[YoussefBelloum]

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]

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

[YoussefBelloum]

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]

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]

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]

I need the scripting. In the back end.

[YoussefBelloum]

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 ?