topic Re: Set analysis to calculate YTD and other ranges of dates in QlikView

Set analysis to calculate YTD and other ranges of dates

Fri, 22 Jul 2016 09:39:11 GMT

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

Re: Set analysis to calculate YTD and other ranges of dates

sunny_talwar — Fri, 22 Jul 2016 11:31:36 GMT

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)

Re: Set analysis to calculate YTD and other ranges of dates

Fri, 22 Jul 2016 15:24:53 GMT

Hi Sunny,

Its working now.

Many thanks for your support

Paulo

Re: Set analysis to calculate YTD and other ranges of dates

Mon, 25 Jul 2016 14:41:18 GMT

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

Re: Set analysis to calculate YTD and other ranges of dates

sunny_talwar — Mon, 25 Jul 2016 14:49:55 GMT

How about this?

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

Re: Set analysis to calculate YTD and other ranges of dates

Mon, 25 Jul 2016 15:32:48 GMT

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)

Re: Set analysis to calculate YTD and other ranges of dates

sunny_talwar — Mon, 25 Jul 2016 16:01:56 GMT

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

Re: Set analysis to calculate YTD and other ranges of dates

Mon, 25 Jul 2016 17:21:23 GMT

Hi,

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

Re: Set analysis to calculate YTD and other ranges of dates

sunny_talwar — Mon, 25 Jul 2016 17:46:53 GMT

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

Re: Set analysis to calculate YTD and other ranges of dates

Mon, 25 Jul 2016 18:07:34 GMT

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.

Re: Set analysis to calculate YTD and other ranges of dates

sunny_talwar — Mon, 25 Jul 2016 18:21:55 GMT

Can you try this:

Date(MakeDate(YEAR(DataSubmissao), MONTH(DataSubmissao), DAY(DataSubmissao))) as DataSubmissao

and this expression:

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

Re: Set analysis to calculate YTD and other ranges of dates

Tue, 26 Jul 2016 14:11:35 GMT

Hi Sunny,

I think now is OK. Is show me the correct values.

Thanks

Paulo