Trend off last 6 month on a KPI

[sophie_bd]

Hello,

i want to a trend off the last 6 month on a KPI, like up or down.

I have use le function linest_m, but ir doesn't work like i want (in this exemple i have use last 2 month to test) :

linest_m(total aggr(if(Sum( {<FACT_Type = {'PO'},
DateAnalyse={"<=$(=Date(Max({<FACT_Type={"PO"}>} DateAnalyse)))"}*
{">=$(=addmonths(Date(Max({<FACT_Type={"PO"}>} DateAnalyse)),-1))"}>} [_Quantite_commandee])*only({1}Period_MonthYear),
Sum( {<FACT_Type = {'PO'},
DateAnalyse={"<=$(=Date(Max({<FACT_Type={"PO"}>} DateAnalyse)))"}*
{">=$(=addmonths(Date(Max({<FACT_Type={"PO"}>} DateAnalyse)),-1))"}>} [_Quantite_commandee])*only({1}Period_MonthYear)
),Period_MonthYear),Period_MonthYear)

Could you help me ?

Thanks,

Sophie

[felipedl]

I'd first check if your expression is working accordingly to fit it to the linest_m function.
Use it in a chart to see if you're not getting set analysis errors.

Felipe.

[johngouws]

Hello Sophie. 

I use this exp in my qvs file. It calculates a 'rolling month' trend based on the period the user selects. The variable is called v_Roll_Period and I defined a few values to it, 13, 18, 24, 30 and 36. You can also replace the var with a value if you want to. You will have to modify it to suite your needs. 

LET e_Rolling_Amount = Replace(Replace('
sum({<tx_Summary={|FUEL|}, [Transaction Base Month]={"<=%(=Year(max([Transaction Date]))*12 + Month(max([Transaction Date])))>=%(=Year(max([Transaction Date]))*12 + Month(max([Transaction Date]))-(%(v_Roll_Period)-1))"}>}Amount)
', '|', Chr(39)), '%', '$');

Remember the sorting in the object itself:

max({1<[Transaction Base Month]={"<=$(=Year(max([Transaction Date]))*12 + Month(max([Transaction Date])))>=$(=Year(max([Transaction Date]))*12 + Month(max([Transaction Date]))-($(v_Roll_Period)-1))"}>}[Transaction Base Month])

In my calendar I have a field [Transaction Base Month] which is : Year(Date)*12 + Month(Date) as [Transaction Base Month]. 

You should end up with something like this.