Set analysis to calculate YTD and other ranges of dates

[#1]

Hi,

I need to calculate SUM of categories for YTD which should be the default calculation value when the user opens the Qv application.

For that i doing the following steps:

1. i have a trigger defined when the application open to set and selected the Max year of Date.

2. then I using this expression to calcultate the the SUM of categories for YTD, but is not working:

   =Sum({<Date={'$(=max(Date))'},[YTD Flag]={1}>} NrCategories)

   The YTD flag is being calculated with the following condition: if(Year2Date([$(vDateFieldName)], 0, 1, $(vToday)),1)

3.then i also need, to add in the same expression a condition to show the total SUM of the dates selected by the user.

For example, the application starts and the expression should give the sum of YTD by default. Then if the user selects 2015 and 2016 the expression should give the sum of the two Years, the same for months and/or quarters.

Any suggestions or ideas that help me move forward?

Many Thanks

Paulo

[sunny_talwar]

I think you are using another set analysis Date = Max(Date) which might be over-riding your YTD Flag here. Try this:

=Sum({<Date = ,[YTD Flag] = {1}>} NrCategories)

[#3]

Hi Sunny,

Its working now.

Many thanks for your support

Paulo

[#4]

Hi Sunny,

After load more data from database I see that the SUM is no working as I expected...

The question is when I select for example 2 years the sum is not updated, for other words, I want the SUM of YTD and that is correct, but if the user makes a selection on the dates the SUM must be update to SUM the values on the range of the selection.

Thank you

Paulo

[sunny_talwar]

How about this?

=Sum({<Date = {"$(='<=' & Date(Max(Date), 'DateFieldFormatHere'))"}, [YTD Flag] = {1}>} NrCategories)

[#6]

Hi Sunny,

Nop.. not working. Is give me error in expression.

I have tried like this

=Sum({<Date = {"$(='<=' & Date(Max(Date), 'MM-DD-YYYY'))"}, [YTD Flag] = {1}>} NrCategories)

[sunny_talwar]

Your date if formatted as MM-DD-YYYY? It is not a timestamp right? Can you share a screenshot of how Date looks?

[#8]

Hi,

In the database Yes is a timestamp, but in Qv is a date in format MM-DD-YYYY

[sunny_talwar]

Do you transform into Date while loading? What is the transformation you do? Can you share the script?

[#10]

In the first transformation load script I have:

MakeDate(YEAR(DataSubmissao), MONTH(DataSubmissao), DAY(DataSubmissao)) AS DataSubmissao

Note: In database is a timestamp field.

Then I'm using the DataSubmissao field to generate the calendar where I rename the column to Date which is my common date:

[DateLink]:

LOAD

	IdCategory
	,DataSubmissao AS Date
	,'Category'			AS DateType

RESIDENT F_Category

;

CALL CalendarGen('Date', 'DateLink', 'Generic', 'Generic ');

CALL CalendarGen('DataSubmissao', 'F_Category', 'Category', 'Category ');

The subroutine to generate the calendar is in attach.