topic Re: How To Calculate MAT in QlikView

How To Calculate MAT

Tue, 02 Jun 2015 12:32:04 GMT

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

Re: How To Calculate MAT

sunny_talwar — Tue, 02 Jun 2015 12:38:27 GMT

Give this a try:

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

Re: How To Calculate MAT

jonathandienst — Tue, 02 Jun 2015 12:43:08 GMT

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])

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

Re: How To Calculate MAT

Colin-Albert — Tue, 02 Jun 2015 12:43:12 GMT

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

Re: How To Calculate MAT

Wed, 03 Jun 2015 11:44:02 GMT

Hi @jonathan dienst,

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

Re: How To Calculate MAT

pratap6699 — Wed, 03 Jun 2015 11:53:05 GMT

try this it should be ur requirement

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

system formated date will display

Re: How To Calculate MAT

Wed, 03 Jun 2015 12:04:24 GMT

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

Re: How To Calculate MAT

Colin-Albert — Wed, 03 Jun 2015 12:15:14 GMT

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.

Re: How To Calculate MAT

sunny_talwar — Wed, 03 Jun 2015 12:16:19 GMT

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

Re: How To Calculate MAT

Mon, 08 Jun 2015 09:25:21 GMT

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

Re: How To Calculate MAT

sunny_talwar — Mon, 08 Jun 2015 10:04:29 GMT

Are you looking for cumulative chart?

Re: How To Calculate MAT

Mon, 08 Jun 2015 10:21:43 GMT

Yess cumulative chart containing sum of sales from last 12 months.

withyou calculation it only takes the sum of current month I dont know why , may be the range we are defining in that formula is correct

Re: How To Calculate MAT

sunny_talwar — Mon, 08 Jun 2015 10:24:00 GMT

For cumulative chart, we will need a RangeSum function together with Above/Below function. Let me work on a solution for you.

Best,

Sunny

Re: How To Calculate MAT

Mon, 08 Jun 2015 10:26:42 GMT

Thanks Sunny

Re: How To Calculate MAT

sunny_talwar — Mon, 08 Jun 2015 10:30:00 GMT

Actually you might not need it, if this is what you are looking for:

I used the inbuilt accumulation functionality of QlikView:

See if this works (attaching the qvw as well)

Best,

Sunny

Re: How To Calculate MAT

Mon, 08 Jun 2015 11:27:19 GMT

No its not working

Re: How To Calculate MAT

sunny_talwar — Mon, 08 Jun 2015 12:01:00 GMT

Hi Sujit,

When you say not working, do you mean its not giving you the desired results? I am not sure what exactly are you trying to get? Will you be able to provide with your expected result?

Best,

Sunny

Re: How To Calculate MAT

Mon, 08 Jun 2015 12:12:21 GMT

Hi sunny,

I mean I am not getting desired results. What I am expecting is :-

Lets say we have a line chart where I have Date on X axis . Now at each point for ex jun-2014 it should show the sum of the sales till July-2013 to Jun-2014. This is what Moving annual total is at all points. here we need to sum tha sales for a range of 12 months including selected month and back 11 months. Please let me know if any other info you need.

Thanks

sujit

Re: How To Calculate MAT

sunny_talwar — Mon, 08 Jun 2015 12:26:28 GMT

I think I understand what you are looking for. Let me see if I can get a sample soon.

Best,

Sunny

Re: How To Calculate MAT

sunny_talwar — Mon, 08 Jun 2015 12:37:50 GMT

May be this?