YTD & LY YTD

[#1]

Hello,

On my sheet the user can select a year and a single month. The idea is that a then display a dashboard with the Current YTD and the LastYear YTD.

But how could i do that, my data is stored on a daily base, but i have no idea on how to sence wich month has been selected ....

Kind Regards, Harry

[#2]

Hi Harry,

To get month you can easily use the MONTH statement in the load script. So, It should look like this:

LOAD

....

MONTH([YOUR_DATE]) AS YOUR_MONTH

FROM...;

If I misunderstood your request, please let me know.

Cheers,

JTPro

[sathishkumar_go]

Hi u have data on daily basis. That means date(Particular date).

base on date u take month and year.

-Sathish

[#4]

At the data level there is no problem, i just don't know how to code the SUM in QV, so that the right data is summed Current Year & LastYear depending on the month the user clicked ...

[#5]

I've seen samples that worked with a REPORT_DATE and so on, but in what i want to achieve my report date is the lastdate of the selected month ....

And summing a month works autmaticaly, and in a graph i can use Full accumulation, but what if i just want to do this in a calculation that is used in a gauge

[sathishkumar_go]

Hi

use sum(fieldname) other wise attach ur application here?

-Sathish

[#7]

This is a test, we started from the Consumer Good Sales and are striping off most of the things. We are still in evaluation mode.

Ive striped the sales data in the script, i hope you understand that we prefer not to pass the real data.

The goad is that on the dashboard page the users selects a year, a single month, and then i what the gauge to display the ytd vs ytd_ly of the selected yead/month.

[#8]

I'll explain maybe a bit more what is the goal.

The users select a year, no period selected then i'm shwing the samethign as when Dec is selected.

ex 2009, no month : YTD = 01/01/2009 till 12/31/2009, LY YTD = 01/01/2008 till 12/31/2008

When the users select Jul

ex 2009, Jul : YTD = 01/01/2009 til 07/31/2009, LY YTD = 01/01/2008 till 07/31/2008

And here it works month by month, calculating for (at DB level) month the YTD works, until no month is selected .... then the total is much to high

[#9]

This is how I do it in our QlikView application -

During data load script, calculate different flags that I need in the main table --

SET LAST_WORKDAY_REPDATE_VAR = date(FieldValue('SYMB_DT', 1)); // this may be Today() in your situation.

UPDATED_GLOBAL_CV_SUMMARY:
LOAD
CMU_1_ID,
ID_TEAM,
REPORTING_DATE,
if(InDay(REPORTING_DATE,$(LAST_WORKDAY_REPDATE_VAR),0)=-1,1,0) AS PrevDayFlag,
if(InMonthToDate(REPORTING_DATE,$(LAST_WORKDAY_REPDATE_VAR),0)=-1,1,0) AS MTDFlag,
if(InYearToDate(REPORTING_DATE,$(LAST_WORKDAY_REPDATE_VAR),0,1)=-1,1,0) AS YTDFlag,
if(InYearToDate(REPORTING_DATE,$(LAST_WORKDAY_REPDATE_VAR),-1)=-1,1,0) AS PrevYTDFlag,
if(InYearToDate(REPORTING_DATE,$(LAST_WORKDAY_REPDATE_VAR),-2)=-1,1,0) AS PrevPrevYTDFlag,
if(InYear(REPORTING_DATE,$(LAST_WORKDAY_REPDATE_VAR),-1)=-1,1,0) AS PrevFullYearFlag,
if(InYear(REPORTING_DATE,$(LAST_WORKDAY_REPDATE_VAR),-2)=-1,1,0) AS PrevPrevFullYearFlag,
if(REPORTING_DATE >= AddMonths(Today(), -1),1,0) AS LastOneMonthFlag,
if(InYearToDate(REPORTING_DATE,MonthEnd(Today(), -1),0,1)=-1,1,0) AS YTDTillLastMonthEndFlag,
RESIDENT
GLOBAL_CV_SUMMARY;

And in the chart, I simply use - sum(AM_NET_CVA_ASGN_DELTA*YTDFlag) as an expression to get YTD sum, sum(AM_NET_CVA_ASGN_DELTA*PrevYTDFlag) for Previous YTD and so on...

Hope this helps..

[#10 · Accepted solution]

In your situation though, I dont think flags can be used as you want to calculate different YTD, Prev YTD at runtime. We had similar problem and had to sacrifice this ability for performance reasons. We used to provide a date picker where user can select the date and then using expressions like one below in the chart we used to calculate YTD amount for the selected date.

sum(if(InYearToDate(REPORTING_DATE,LAST_WORKDAY_REPDATE_VAR,0,1)=-1,AM_NET_CVA_ASGN_DELTA,0))

LAST_WORKDAY_REPDATE_VAR is the variable that was attached to the date picker... You might be able to do something similar based on selected Month...