Value M vs Value M-1 in a table chart

[guillaume_gorli]

Dear all,

I need to show in a table chart :

• inventory value of the current month in selection
• inventory value of the previous month
• Difference between M and M-1 inventory value

Thanks to some nice help, i have been able to show in a text object the inventory value of the provious month but as i am now trying to paste the formulas in a table chart, the value is shown but linked to the previous month...

let me show you :

For instance :

In october 2016 :

Inventory value is 1254

Inventory value of the previous month is 408 (september figure then)

Difference between M & M-1 is 846

Below is print screen of what i get : the text object is right but the data in the table chart is wrong

And this is what i am looking for :

Attached is .qvw and excel database for those who want to help

Thanks in advance

Guillaume

[sunny_talwar]

The table should show for each month of the selected year  the inventory value of the current month vs the inventory value of December the previous year.

Each month of the selected year? So, if 2017 is selected show all months (Jan to Dec)?? Regardless of selection in Month field? and always show December from Previous Year?

Also, I see Inventory Variation YTD, is this needed also?

[guillaume_gorli]

Each month of the selected year? So, if 2017 is selected show all months (Jan to Dec)?? Regardless of selection in Month field? and always show December from Previous Year? YES

Also, I see Inventory Variation YTD, is this needed also? Inventory variation YTD should be the output of the requested data : for each month of the current year, it should compare the inventory value of the month vs the innvetory value of December from the previous year

[sunny_talwar]

check attached

[guillaume_gorli]

We are nearly there :

The output is correct although as FEB 2017 is selected, i do not want to show in the table DEC 2016 but only the results for the months of the selected year (meaning here january and February 2017)

[sunny_talwar]

Check attached

[guillaume_gorli]

Perfect !

What did you change Sunny, i see no difference in the formulas ?

[sunny_talwar]

That's for you to find.... just kidding...

Inventory value changed from

Sum({$<MonthYear = {"$(='>=' & Date(AddYears(YearStart(Max(MonthYear)), -1), 'MMM YYYY') & '<=' & Date(Max(MonthYear), 'MMM YYYY'))"}, Mois, Année>}[Inventory Value])

to

Sum({$<MonthYear = {"$(='>=' & Date(YearStart(Max(MonthYear)), 'MMM YYYY') & '<=' & Date(Max(MonthYear), 'MMM YYYY'))"}, Mois, Année>}[Inventory Value])

Inventory M-1 changed from

Above(Sum({$<Mois, Année, MonthYear>}[Inventory Value])) * Avg({$<MonthYear = {"$(='>=' & Date(AddYears(YearStart(Max(MonthYear)), -1), 'MMM YYYY') & '<=' & Date(Max(MonthYear), 'MMM YYYY'))"}, Mois, Année>} 1)

to

Above(Sum({$<Mois, Année, MonthYear>}[Inventory Value])) * Avg({$<MonthYear = {"$(='>=' & Date(YearStart(Max(MonthYear)), 'MMM YYYY') & '<=' & Date(Max(MonthYear), 'MMM YYYY'))"}, Mois, Année>} 1)

Rest all stayed the same

[guillaume_gorli]

Thanks for all !

[sunny_talwar]

Glad to help my friend

[guillaume_gorli]

Sunny,

When the inventory value is equal to zero, the ytd variation is not properly shown :

For instance : Below image is correct

But if for some reasons, the inventory value of a specific month is equal to zero (which could happen), then the YTD inventory variation is not correct : for instance if i key zero for january 2017 in the test database, it shows a blank results although it should show in the example - 597

Would it be because of the sign "not equal to zero" ?

If([Inventory value] <> 0, [Inventory value] - Sum(TOTAL {$<MonthYear = {"$(=Date(AddMonths(YearStart(Max(MonthYear)), -1), 'MMM YYYY'))"}, Mois, Année>}[Inventory Value]))

Guillaume