topic Re: MAT creation in QlikView

MAT creation

konidena — Wed, 25 Nov 2020 16:16:04 GMT

Hi Team,

I have a data about

Year, Month, SalesAmount (StdUnits).

Now, my requirement is, i have to create a line chart with below expressions.

1. Dimension: Month

2. Expression: Sum(SalesAmount(StdUnits).

Question: as per my knowledge , MAT is the last 12 months total. for example, for month Jan 2016 , the MAT is sum of sales from Feb 2015 to Jan 2016

I have just created text with the expression "=sum({<Year={$(=max(Year))},Month={"<=$(=Max(Month))"}>}[Sales Amount (Std Unit)])+sum({<Year={$(=max(Year)-1)},Month={">$(=Max(Month))"}>}[Sales Amount (Std Unit)])". It is giving the correct total in text box.

If i use the same expression in my chart , it is not showing correctly.

I have data from 2014 May to 2016 Apr. So, if i select 2016 as my year, then it should show the chart dimension for

Jan, Feb, Mar, Apr. But it is showing all the months from Jan to Dec.

Actually it should show MAT for Jan, Feb, Mar , Apr.

Please help me in getting this MAT.

Note: I am using the qlikview personal edition. So, Please copy the code here as i am not able to open the qvw from my personal edition.

Attaching the sample data and qvw here.

Regards

Srinivas

Re: MAT creation

sunny_talwar — Thu, 05 May 2016 15:12:35 GMT

Why would you want to see only Jan, Feb, Mar and Apr for 2016 if MAT is last 12 months? Would you be able to elaborate?

Re: MAT creation

konidena — Fri, 06 May 2016 01:51:20 GMT

I have data in 2016 till April. If I select 2016 as my year then it should show the MAT for 2016 Jan, Feb, Mar, Apr.

If the Year is 2015, then Jan to Dec MAT should show for each month.

Re: MAT creation

sunny_talwar — Fri, 06 May 2016 02:38:17 GMT

Can you check now, I have used the As-OfTable Approach in the chart

The As-Of Table

Re: MAT creation

settu_periasamy — Fri, 06 May 2016 02:39:11 GMT

Hi,

Can you tell, Why did you sum the ">Month" expression? if you remove that it will also give the same result..

for 2016, it shows Jan to Apr data.. Look the below screen shot..

Re: MAT creation

sunny_talwar — Fri, 06 May 2016 02:41:50 GMT

From what I have heard MAT is sum for last 12 months. So for Jan 2016, it would be a sum of all numbers from Feb 2015 till Jan 2016. I think it might be doable using Above function, but I have used the AsOfTable approach above. Can you also check to see if it make sense

Re: MAT creation

settu_periasamy — Fri, 06 May 2016 02:58:06 GMT

Yes sunny. I didn't read the OP properly. Your suggestion makes sense.

Re: MAT creation

konidena — Wed, 11 May 2016 06:38:08 GMT

Hi Sunny,

Can you please help me in displaying the 12 months on the X-Axis.

for MAT, if we consider 'MAY' is current month then the x-axis should display data from jun-2015 to may -2016.

Please let me know if you need any additional info.

Regards

Srinivas

Re: MAT creation

sunny_talwar — Wed, 11 May 2016 08:24:14 GMT

Check if the attached is what you are looking for.

Expression used

=Sum({<Month, Year, ReportYearMonth = {"$(='>' & Date(AddMonths(Max(ReportYearMonth), -12), 'YYYYMM') & '<=' & Date(AddMonths(Max(ReportYearMonth), 0), 'YYYYMM'))"}, ReportYear, ReportMonth>}[Sales Amount (Std Unit)])

Re: MAT creation

konidena — Thu, 12 May 2016 16:00:08 GMT

Thanks Sunny for your time .

This is creating the correct dimension.

I have a issue here. I have to calculate Evaluation Index.

evaluation index= current Year market share / Previous Year Market share

Market share=Sales Amount (Std Unit)/Market Sales (Std Unit)

As per the above expression you written, we made 'Year' as constant.. It will not change.

=Sum({<Month, Year, ReportYearMonth = {"$(='>' & Date(AddMonths(Max(ReportYearMonth), -12), 'YYYYMM') & '<=' & Date(AddMonths(Max(ReportYearMonth), 0), 'YYYYMM'))"}, ReportYear, ReportMonth>}[Sales Amount (Std Unit)])

But, while calculating evolution index, i need to have previous year market share as well as current year market share.

Please help me here.

Regards

Srinivas

Re: MAT creation

sunny_talwar — Thu, 12 May 2016 16:28:33 GMT

Can you share an updated sample with the expected output?

Re: MAT creation

konidena — Thu, 12 May 2016 16:59:34 GMT

I have attached the app.

Expected Output is :

selections:

Standard Units in (Measure field: The original Field is "Column 1" in data model)

and in TimeInterval, "MAT" should be selected

I have columns fields in the data as

Sales Amount (Std Unit)

Market Sales (Std Unit)

my calculation for Market Share is : Sales Amount (Std Unit)/Market Sales (Std Unit)

Evolution Index Formula is : current Year Market Sale / Previous Year Market Share

Chart expectation: X-Axis should display months from Apr 2016 to May 2015 (Data I have till apr 2016)

Y - Axis: It should show the evolution index when MAT is selected as the option in the field "TimeInterval"

You have provided the expression for displaying the data from Apr 2016 to May 2015. In that expression, the set analysis is showing the Year as static value.

Now, Evolution Index will be calculated based on the Current Year, and Previous Year. So, it is not possible with static value "Year" in set Analysis.

Please let me know if you want more info on this.

Regards

Srinivas