Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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'
Hi Peter,
This document may help you with prior period comparisons:
http://community.qlik.com/docs/DOC-4313
Regards,
Steve
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
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.
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 <mailto:petem@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>
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.
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 <mailto:petem@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>
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.
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 <mailto:petem@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>