How To Calculate MAT

[#1]

Hi

I have a data set in which I want to calculate MAT and I amusing this formula to calculate

Sum({<TYPE={'DATA'},XDate={'>=$(vDate) <$(vDate_1)'}>}[SALES])

Where vDate is a variable which has the value for current year date and vDate_1 has the same date of last year

but it is not showing correct value

BRs

[sunny_talwar]

Give this a try:

Sum({<TYPE={'DATA'},XDate={"$(='>=' & $(vDate) & '<' & $(vDate_1))"}>}[SALES])

[jonathandienst]

The XDate conditions should be in double quotes, rather than single quotes, and you need some = signs:

Sum({<TYPE={'DATA'},XDate={">=$(=vDate) <$(=vDate_1)"}>}[SALES])

or

Sum({<TYPE={'DATA'},XDate={">=$(=Date(vDate)) <$(=Date(vDate_1))"}>}[SALES])

The problem comes down to the date formats and the actual dates in the XDate field and the vDate* variable definitions. I suggest that you upload a sample to prevent a lot of incorrect guesses as the problem.

Preparing examples for Upload - Reduction and Data Scrambling

In the meantime - does XDate have a time component?

If you removed the time component, did you do it properly ( Date(myTimeStamp) does not remove the time )?

Do the VDate* variables have the same format as XDate?

Are these variables globally evaluated (in other words, they start with = )?

etc

etc

[Colin-Albert]

I would also check that the format of the dates in your variables vDate and vDate_1 match the format of the XDate field.

[#5]

Hi @jonathan dienst,

Here is the sample file where I want to calculate MAT and YTD using this data. for these two charts

[pratap6699]

try this it should be ur requirement

Sum({<TYPE={'DATA'},XDate={">=$(=Date#(vDate)) <$(=Date#(vDate_1))"}>}[SALES])

system formated date will display

[#7]

Hi

can you use the attached sample file and try using the formula....

I did it but it was not working

may be you can solve this by writing the code

many thanks

[Colin-Albert]

Date# will not help here as Date# controls the input format.

Sujit,

I would test your date formats by putting $(vDate) and $(vDate_1) into separate textboxes and comparing the date formats shown with a listbox displaying the XDate field.

[sunny_talwar]

Not sure which chart you wanted us to work on, but I thought the bottom one made sense. Is this what you are looking for:

Where without any selection, you can see 12 month of data? If yes then you can use this expression:

1) Sum({<TYPE={'DATA'}, vDate = {"$(='>=' & Date(AddYears(Max(vDate), -1), 'MMM-YY') & '<=' & Date(Max(vDate), 'MMM-YY'))"}>}[SUM of Monthly LC])

2) Sum ({<TYPE={'LU'}, vDate = {"$(='>=' & Date(AddYears(Max(vDate), -1), 'MMM-YY') & '<=' & Date(Max(vDate), 'MMM-YY'))"}>}[SUM of Monthly LC])

3) Sum ({<TYPE={'FBP'}, vDate = {"$(='>=' & Date(AddYears(Max(vDate), -1), 'MMM-YY') & '<=' & Date(Max(vDate), 'MMM-YY'))"}>}[SUM of Monthly LC])

Attaching your qvw for reference purposes.

Best,

Sunny

[#10]

Hi SunIndia,

You can check its not working; Its only showing the sum for a particular month not the moving annual total for that month