Calcualtion of YTD, QoQ, YTS previous year

[#1]

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

[buzzy996]

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)')

[#3]

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

[oknotsen]

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?

[buzzy996]

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

[#6]

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

[maxgro]

PFA

[#8]

Please find the attached sample file for my data. In case if u can derive any idea after seeing this file.

Regards,

Akshay

[oknotsen]

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).

[#10]

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