
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Calcualtion of YTD, QoQ, YTS previous year
Hi,
Data is displayed in form of Q1,Q2,Q3 and Q4 for years 2010-2014. I want to calculate a table for YTD, QoQ, QoQ vs Previous Year and YTD vs Previous Year. How to draw that table using quarter values.
Regards,
Akshay
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I changed the bold part to get a Year and Quarter that works with my previous chart expressions
Final:
LOAD
if( IsNum(Left(Dates,2)),1,0) as Flag,
year((Date#(Right(Dates,2),'YY'))) as Year,
dual(Left(Dates,2), right(Left(Dates,2),1)) as Quarter,
*
Resident T2
Where Not if(IsNum(Left(Dates,2)),1,0);
DROP Table T2;
this is the result with your data

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
try the below ones,
ytd:
CY
=num(sum({$<Year = {$(=only(Year))}, Month = {"<=$(=max(Month))"}>} LineSalesAmount), '$#,##0;($#,##0)')
PY
=num(sum({$<Year = {$(=only(Year) - 1)}, Month = {"<=$(=max(Month))"}>} LineSalesAmount), '$#,##0;($#,##0)')
qtd:
CY
=num(sum({$<Year = {$(=only(Year))}, Quarter = {$(=only(Quarter))}, Month = {"<=$(=max(Month))"}>} LineSalesAmount), '$#,##0;($#,##0)')
PY
=num(sum({$<Year = {$(=only(Year) - 1)}, Quarter = {$(=only(Quarter))}, Month = {"<=$(=max(Month))"}>} LineSalesAmount), '$#,##0;($#,##0)')

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hey Shiva,
Thanks for the reply. This is not working as my datas are quaterly. So there is no month in data file. Please refer the sample file. If it will help to have more clear picture on my query.
So i want to have a table, which will have YTD and YTD vs PY in percentage (Growth from CY to PY)
Thanks and Regards,
Akshay


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I know YTD is YearToDate, but what does QoQ, PY and CY stand for?
You say you do not have Month is your data files, but do you have dates in your data files? Or, in different words, how does your data look like?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
i think so ,it's difficult to calculate YTD with out month filed,if u don't have month field if possible try to create else look at ur year field..just focus on that field from which date field ur getting that year and use the same field to extract month from that in the above expression it self instituted of creating the same field

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello Onno,
I cann't share the original data file due to confidentiality of data. Please find the attached dummy file of my original data. The data are in given time frame. And also find the attached scrpit for uploading the data. PY and CY stands for 'previous year' and 'current year' respectively.
Regards,
Akshay

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
PFA

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Please find the attached sample file for my data. In case if u can derive any idea after seeing this file.
Regards,
Akshay


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I don't understand your YTD-Sample1.png; it just shows a dashboard of which it is unclear to me what you are trying to show.
But about your problem at hand. Your data is not stored as actual dates but as pieces of text that represent quarters in a specific year. It would be so much easier if they would be stored as a date (like for example the first day of the quarter).
If you can convert your data in some way to an actual date, you can use functions like YearToDate() in your script to easily flag these things. Now you have to do some manually flagging (using a mapping table comes to mind).

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello Massimo,
Thanks for reply. I am using right now trial version of qlikview, so cann't open the file created by other users. If possible, can you provide the screenshots of your solutions.
Thanks and regards,
Akshay

- « Previous Replies
-
- 1
- 2
- Next Replies »