topic Re: Calculate data from specific dates in QlikView

Calculate data from specific dates

bharatkishore — Wed, 25 Oct 2017 12:16:58 GMT

Hi All,

I have data from 2014 Jan to 2019-Sep.

Now i need to calculate data from 2015 - Jan to 2016 - Dec.

It should not be hard-coded, and every year it should change automatically.

Kindly help.

Thanks,

Bharat

Re: Calculate data from specific dates

its_anandrjs — Wed, 25 Oct 2017 12:26:44 GMT

Elaborate more

1. Do you have date fields in model

2. You need to load data

3. Or else you need to calculate data.

Re: Calculate data from specific dates

bharatkishore — Wed, 25 Oct 2017 12:31:38 GMT

Hi Anand,

I have date field below image for reference:

I have data from Jan 2014 to Sep 2017.

I need to calculate sum(myfield) from Jan 2015 to Dec 2016.

Please let me know if you require anything more.

Thanks,

Bharat

Re: Calculate data from specific dates

its_anandrjs — Wed, 25 Oct 2017 12:33:14 GMT

Loading data from table try this

Load

From Source Where

Datefield <= YearStart(AddYears( Max(Datefield), -3)) and

Datefield >= YearEnd(AddYears( Max(Order_Date), -2))

Re: Calculate data from specific dates

its_anandrjs — Wed, 25 Oct 2017 12:37:14 GMT

Your date is in string format or num format.

Or can you provide sample data.

Re: Calculate data from specific dates

bharatkishore — Wed, 25 Oct 2017 12:38:56 GMT

Thanks for the reply.

PFA qvw for more understanding.

Thanks,

Bharat

Re: Calculate data from specific dates

bharatkishore — Wed, 25 Oct 2017 12:46:59 GMT

pfa

Re: Calculate data from specific dates

qlikview979 — Wed, 25 Oct 2017 12:52:41 GMT

May be like this

sum({<Monthyear={ >=$ (=Yearstart(Monthyear),-3) <=$ (=Yearend(Monthyear),-1) }>}Myfield)

-Mahesh

Re: Calculate data from specific dates

its_anandrjs — Wed, 25 Oct 2017 12:55:37 GMT

Check the attached

Expre:-

=sum({<Year={">=$(=Max(Year)-1) <=$(=Max(Year))"},Month={">=$(=Max(Month)-12) <=$(=Max(Month))"}>}SystemYr_sql)

Re: Calculate data from specific dates

bharatkishore — Wed, 25 Oct 2017 13:03:46 GMT

Hi Anand,

Request you please find the attached file and there i have derived a new MonthYear field.

With this can we achieve and i need data from 2015 Jan to 2016 Dec.

In your file it is Jan 2016 to Sep 2017.

Please let me know if you require anything more.

Thanks,

Bharat

Re: Calculate data from specific dates

its_anandrjs — Wed, 25 Oct 2017 13:10:24 GMT

This is not required as you have Year and Month fields as well as so why you do with Month Year field. Is this required let me know.

Re: Calculate data from specific dates

its_anandrjs — Wed, 25 Oct 2017 13:13:39 GMT

Also you dont have data for Dec 2017 in data model.

Re: Calculate data from specific dates

bharatkishore — Wed, 25 Oct 2017 13:14:30 GMT

I will be using MonthYear only, since i will be using this field in various charts.

So that is why i will be using only MonthYear.

Please let me know if you need anything else..

Re: Calculate data from specific dates

bharatkishore — Wed, 25 Oct 2017 13:15:20 GMT

No i need to calculate from Jan 2015 to Dec 2016 only not 2017 data...

Re: Calculate data from specific dates

its_anandrjs — Wed, 25 Oct 2017 13:31:16 GMT

This will give you proper result then why go for new field and new field format gives you only MM/DD/YYYY date format then why go for that. You have to go for this

Output

Re: Calculate data from specific dates

bharatkishore — Wed, 25 Oct 2017 13:40:00 GMT

Thanks Anand..

Sure i will use this one, but the last thing is if we un-select the 2016 data then it default dates are changing.. i needed without selecting anything the dates are 2015 Jan to 2016 Dec.

Sorry for asking more.. but this is what i needed. Below image for reference:

Defaultly without selecting anything it should show from Jan 2015 to Dec 2016

Re: Calculate data from specific dates

its_anandrjs — Wed, 25 Oct 2017 15:30:08 GMT

Then in formula use this expression

=sum({<Year={">=$(=Max(Year)-2) <=$(=Max(Year)-1)"},Month={">=$(=Max(Month)-12) <=$(=Max(Month))"}>}SystemYr_sql)

Re: Calculate data from specific dates

bharatkishore — Thu, 26 Oct 2017 05:01:50 GMT

Thanks a lot Anand.