How to show my data always to be MTD and YTD

[prabunakil]

Hi, now my practice is when a start new month, i will go and select my qvw. file choose the latest month and save it.

Is there anyway i can show my data always with latest month to date and year to date.

The data file i have is from 2015 till date.

[Anil_Babu_Samineni]

Yes, You can define many ways for MTD / YTD. Can you share logic what you need?

[Mark_Little · Accepted solution]

Hi,

For this can of thing i always create flags within my calendar in the script. That way it makes the set analysis easier and updates with every load

Something like this

IF(YEAR(TempDate)= YEAR(TODAY(1)) AND TempDate <= TODAY(1),1) AS F_CAL_YTD,

IF(YEAR(TempDate)= YEAR(TODAY(1)) AND MONTH(TempDate)= MONTH(TODAY(1)) AND TempDate <= TODAY(1),1) AS F_MTD,

Then Set analysis would be

SUM({<F_CAL_YTD={1}>}Values)

Mark

[prabunakil]

Hi mark, this will be in load script or dimension of the field?

[prabunakil]

Please check how i define my month and year.

Right(DAT,2) as TransactionDay,

Mid(DAT,6,2) as TransactionMonth,

Left(DAT,4) as TransactionYear,  

if(num(Mid(DAT,6,2))<=3, Left(DAT,4)-1 & '/' & Left(DAT,4), Left(DAT,4) & '/' & text(Left(DAT,4)+1)) AS [Fiscal Year],

[Mark_Little]

Hi,

It will created new fields within your data, The fields will contain a 1 when they match the criteria. For example any date that is in the current MTD the field F_MTD will contain a 1.

This can then be referenced in the Set analysis.

Mark

[prabunakil]

Hi Can i have a example of the qvw.

[prabunakil]

thank you it works.