Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

petermoyaert
New Contributor II

year to date current year and last year / same period last year

In my Qlikview table users need to select

* year

* month

If for example the user selects year 2013 and month October I want to get following information

- Sales current month : 01/10/2013-04/10/2013

- Sales same period Last Year : 01/10/2012-04/10/2012

- Sales current year YTD : 01/01/2013-04/10/2013

- Sales same peiode YTD LY : 01/01/2012-04/10/2012

And above this, I want to have a header for each colomn, stipulating the period :

- Sales 04/10/2013 month

- Sales 04/10/2012 month

- Sales 04/10/2013 YTD

- Sales 04/10/2012 YTD

Can someone help me with both the formulas and headers ?

Thanks a lot for your feedback.

9 Replies

Re: year to date current year and last year / same period last year

Not enough information really.

Assumptions: you have a field named 'year', a field named 'month', a field named 'date' and a field named 'Sales'. All these fields are numeric fields.

Sales current month: sum({<date={'<=$(=max(date))'}>} Sales)

Sales same period last year: sum({<date={'>=$(=addyears(monthstart(max(date)),-1)<=$(=addyears(max(date),-1) '},month=,year=>} Sales)

Sales current year: sum({<date={'<=$(=max(date))'},month=>} Sales)

Sales YTD LY: sum({<date={'>=$(=addyears(yearstart(max(date)),-1)<=$(=addyears(max(date),-1) '},month=,year=>} Sales)

labels:

='Sales ' & date(max(date),'DD/MM/YYYY') & ' month'

='Sales ' & date(addyears(max(date),-1),'DD/MM/YYYY') & ' month'

='Sales ' & date(max(date),'DD/MM/YYYY') & ' YTD'

='Sales ' & date(addyears(max(date),-1),'DD/MM/YYYY') & ' YTD'


talk is cheap, supply exceeds demand
MVP
MVP

Re: year to date current year and last year / same period last year

Hi Peter,

This document may help you with prior period comparisons:

http://community.qlik.com/docs/DOC-4313

Regards,
Steve

http://www.quickintelligence.co.uk/

petermoyaert
New Contributor II

Re: year to date current year and last year / same period last year

Thanks for the feedback.

However, additional question

In my Qlikview table people need to select "Year" and "month" but not necessarily "date".

If no date is selected, the system should take the latest available date for calculating sales.

For example : today 05/10/2013

If a user selects 2013 and september : system should take into account sales until 30/09/2013

If a user selects 2013 and october : system should take into account until 05/10/2013

Re: year to date current year and last year / same period last year

If users select a date then that date will be used for the calculations as the To-Date date. If no date is selected then the maximum date that exists in the selected month of the selected year will be used.


talk is cheap, supply exceeds demand
petermoyaert
New Contributor II

Re: year to date current year and last year / same period last year

Sorry , but the formula is not working : sum({<%Key_Datum={'<=$(=max(%Key_Datum))'}>}Waarde_Levering)

It results in YTD figure

Met vriendelijke groeten - Best regards

Peter Moyaert

Chief Financial Officer

<http://www.laroyduvo.be/?utm_source=emailsignature&utm_medium=email&utm_term=&utm_content=&utm_campaign=corporate>

Industrieweg 98 - 100

9032 Wondelgem

Belgium

www.laroyduvo.be<http://www.laroyduvo.be/?utm_source=emailsignature&utm_medium=email&utm_term=&utm_content=&utm_campaign=corporate>

www.cede.be<http://www.cede.be/>

T.

M.

F.

E.

+3292571344

+32476535783

+3292571358

petem@laroyduvo.be <mailtoSmiley Tongueetem@laroyduvo.be>

Follow us:

<http://goo.gl/mRru5>[Laroy Duvo on facebook]<http://goo.gl/J02HF>[Laroy Duvo on twitter]<http://goo.gl/RG2Tf>[Laroy Duvo on google plus]<http://goo.gl/BiAm5>

Van: G Wassenaar

Verzonden: vrijdag 4 oktober 2013 21:01

Aan: Peter Moyaert

Onderwerp: Re: - year to date current year and last year / same period last year

QlikCommunity<http://community.qlik.com/>

year to date current year and last year / same period last year

reply from G Wassenaar<http://community.qlik.com/people/gwassenaar?et=watches.email.thread> in Creating Analytics - View the full discussion<http://community.qlik.com/message/401001?et=watches.email.thread#401001>

Re: year to date current year and last year / same period last year

Sorry , but the formula is not working : sum({<%Key_Datum={'<=$(=max(%Key_Datum))'}>}Waarde_Levering)

It results in YTD figure

Then you didn't select a month.


talk is cheap, supply exceeds demand
petermoyaert
New Contributor II

Re: year to date current year and last year / same period last year

Sorry, it is ok now.

However our users normally select months january to current month.

Or I need another formula, Or i need to change their way of working.

Current month and current year YTD is working

Last year formulas do not result in a figure.

Met vriendelijke groeten - Best regards

Peter Moyaert

Chief Financial Officer

<http://www.laroyduvo.be/?utm_source=emailsignature&utm_medium=email&utm_term=&utm_content=&utm_campaign=corporate>

Industrieweg 98 - 100

9032 Wondelgem

Belgium

www.laroyduvo.be<http://www.laroyduvo.be/?utm_source=emailsignature&utm_medium=email&utm_term=&utm_content=&utm_campaign=corporate>

www.cede.be<http://www.cede.be/>

T.

M.

F.

E.

+3292571344

+32476535783

+3292571358

petem@laroyduvo.be <mailtoSmiley Tongueetem@laroyduvo.be>

Follow us:

<http://goo.gl/mRru5>[Laroy Duvo on facebook]<http://goo.gl/J02HF>[Laroy Duvo on twitter]<http://goo.gl/RG2Tf>[Laroy Duvo on google plus]<http://goo.gl/BiAm5>

Van: G Wassenaar

Verzonden: zaterdag 5 oktober 2013 10:01

Aan: Peter Moyaert

Onderwerp: Re: - year to date current year and last year / same period last year

QlikCommunity<http://community.qlik.com/>

year to date current year and last year / same period last year

reply from G Wassenaar<http://community.qlik.com/people/gwassenaar?et=watches.email.thread> in Creating Analytics - View the full discussion<http://community.qlik.com/message/401130?et=watches.email.thread#401130>

Re: year to date current year and last year / same period last year

If users select more months and you want to use only the last month of that selection then you need to add month={$(=max(month))} to the set modifier.

The rest I cannot troubleshoot without looking at the data. Can you post an example document? Reduce and scramble the data where necessary so a small and nonconfidential document remains. See Preparing examples for Upload - Reduction and Data Scrambling.


talk is cheap, supply exceeds demand
petermoyaert
New Contributor II

Re: year to date current year and last year / same period last year

See below print screen of the table

Table at the top is with the correct figures

Table below is with your formula. You will notice that for current year figures are ok. For last year there is an issue (see column 2 with figures)

Could you have another look at it ?

Best regards

Met vriendelijke groeten - Best regards

Peter Moyaert

Chief Financial Officer

<http://www.laroyduvo.be/?utm_source=emailsignature&utm_medium=email&utm_term=&utm_content=&utm_campaign=corporate>

Industrieweg 98 - 100

9032 Wondelgem

Belgium

www.laroyduvo.be<http://www.laroyduvo.be/?utm_source=emailsignature&utm_medium=email&utm_term=&utm_content=&utm_campaign=corporate>

www.cede.be<http://www.cede.be/>

T.

M.

F.

E.

+3292571344

+32476535783

+3292571358

petem@laroyduvo.be <mailtoSmiley Tongueetem@laroyduvo.be>

Follow us:

<http://goo.gl/mRru5>[Laroy Duvo on facebook]<http://goo.gl/J02HF>[Laroy Duvo on twitter]<http://goo.gl/RG2Tf>[Laroy Duvo on google plus]<http://goo.gl/BiAm5>

Van: G Wassenaar

Verzonden: zaterdag 5 oktober 2013 10:28

Aan: Peter Moyaert

Onderwerp: Re: - year to date current year and last year / same period last year

QlikCommunity<http://community.qlik.com/>

year to date current year and last year / same period last year

reply from G Wassenaar<http://community.qlik.com/people/gwassenaar?et=watches.email.thread> in Creating Analytics - View the full discussion<http://community.qlik.com/message/401132?et=watches.email.thread#401132>

Community Browser