topic Re: Value M vs Value M-1 in a table chart in QlikView

Value M vs Value M-1 in a table chart

guillaume_gorli — Fri, 10 Mar 2017 11:44:15 GMT

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

Re: Value M vs Value M-1 in a table chart

sunny_talwar — Fri, 10 Mar 2017 11:48:21 GMT

Use Above() function to get the previous dimension's value

Above(Sum ({$ <Mois= {JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC}>}[Inventory Value]))

Re: Value M vs Value M-1 in a table chart

guillaume_gorli — Fri, 10 Mar 2017 12:02:46 GMT

Sunny,

it does not seem to work when data are extended to the next year :

For instance i added 2017 in the test database and for january 2017, the formulas is not working although it should show 597 and 153

Re: Value M vs Value M-1 in a table chart

sunny_talwar — Fri, 10 Mar 2017 12:06:15 GMT

Slightly modified version of the expression

Above(Sum ({$ <Mois= {JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC}, Année>}[Inventory Value])) * Avg({$ <Mois= {JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC}>} 1)

Re: Value M vs Value M-1 in a table chart

guillaume_gorli — Fri, 10 Mar 2017 12:29:27 GMT

Synny your are fantastic.

One last thing i may ask : is there a magical trick to use to show in the table only the provious periods of the current selection.

For instance when you select Feb 2017, only January 2017 and February 2017 to be shown in the table but not March 2017 (even if there is a result for "March inventory variation")

The idea here is not to get the application polluted showing fake figures to users. For instance, for March 2017, there is no inventory value, then the inventory variation for march is obviously fake which i would like not to show to users.

Indeed, next target is to use the Inventory variation formula in other tables trying to built a complete P&L for each period

Thanks for all

Guillaume

Re: Value M vs Value M-1 in a table chart

sunny_talwar — Fri, 10 Mar 2017 12:38:09 GMT

So, basically see the YTD? Try this

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)

Re: Value M vs Value M-1 in a table chart

guillaume_gorli — Fri, 10 Mar 2017 12:40:22 GMT

Thanks a lot Sunny.

As always you are a big help

Guillaume

Re: Value M vs Value M-1 in a table chart

guillaume_gorli — Fri, 17 Mar 2017 12:05:10 GMT

Sunny,

One last thing i try to show :

i need now to build an inventory variation YTD : meaning that it has to compare the inventory value of the current month to the inventory value of Decembre the previous year :

Any idea ?

Guillaume

Re: Value M vs Value M-1 in a table chart

sunny_talwar — Fri, 17 Mar 2017 12:09:11 GMT

Not sure I understand? Is the image above the output you expect to see? On what selections?

Re: Value M vs Value M-1 in a table chart

guillaume_gorli — Fri, 17 Mar 2017 12:11:34 GMT

Yes the image is the output i am looking for with Feb 2017 as selection.

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.

Re: Value M vs Value M-1 in a table chart

sunny_talwar — Fri, 17 Mar 2017 12:15:26 GMT

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?

Re: Value M vs Value M-1 in a table chart

guillaume_gorli — Fri, 17 Mar 2017 12:19:24 GMT

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

Re: Value M vs Value M-1 in a table chart

sunny_talwar — Fri, 17 Mar 2017 12:29:09 GMT

check attached

Re: Value M vs Value M-1 in a table chart

guillaume_gorli — Fri, 17 Mar 2017 12:39:35 GMT

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)

Re: Value M vs Value M-1 in a table chart

sunny_talwar — Fri, 17 Mar 2017 12:45:43 GMT

Check attached

Re: Value M vs Value M-1 in a table chart

guillaume_gorli — Fri, 17 Mar 2017 12:48:13 GMT

Perfect !

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

Re: Value M vs Value M-1 in a table chart

sunny_talwar — Fri, 17 Mar 2017 12:52:22 GMT

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

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)

Rest all stayed the same

Re: Value M vs Value M-1 in a table chart

guillaume_gorli — Fri, 17 Mar 2017 12:53:45 GMT

Thanks for all !

Re: Value M vs Value M-1 in a table chart

sunny_talwar — Fri, 17 Mar 2017 12:57:48 GMT

Glad to help my friend

Re: Value M vs Value M-1 in a table chart

guillaume_gorli — Fri, 17 Mar 2017 14:47:30 GMT

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