Problems with the Last Transaction Date

[juan-carlos]

Hello everyone,

I have a problem that is taking me several days and I'm not sure why it does not work.

On the one hand I have a set analysis (which works perfectly) and returns the combination month of the last movement with units> 0 (Example 2018 Jan) of my products.

Month (Max ({<[Units] = {'> 0'}>} YearMonthNum)) & '' & Year (Max ({<[Units] = {'> 0'}>} YearMonthNum))

If I put it in a variable (vG.App.Last.Amortization.YearMonthText), Sense calculates it correctly and returns me for each product the last month in which it had movement. If I want to use it in another Set analysis like the following:

if (Type <> 'A', $ (v.KPI.Current.Depreciation.Units), if (Type = 'A', Sum ({<[Data type] = {'4'}, [MonthYearText] = { "$ (vG.App.Last.Amortization.YearMonthText)"}, Vision = {'Contract'}>} [Actual units amortization])))

it does not return anything to me and the only way that it works is that it modifies my variable vG.App.Last.Amortization.YearMonthText and adds it = at the beginning

= Month (Max ({<[Units] = {'> 0'}>} YearMonthNum)) & '' & Year (Max ({<[Units] = {'> 0'}>} YearMonthNum))

The problem is that then only the highest date of all movements is calculated and the products whose amortization date is lower than the highest date of the whole set do not appear.

Any idea what I'm doing wrong ???

[stigchel]

Not sure how your If(Type is associated with your desired months, but in general it looks like you want a different value per a product(?) dimension and $ sign expansions are evaluated at the chart level  and not on a row by row value of your dimension. Maybe you can include it as a calculated dimension?

[juan-carlos]

Hi Piet...thanks for your fast answer:

Regarding it looks like you want a different value per a product(?) Yes exactly

Maybe you can include it as a calculated dimension? I did it...in fact if I use

Month (Max ({<[Units] = {'> 0'}>} YearMonthNum)) & '' & Year (Max ({<[Units] = {'> 0'}>} YearMonthNum))

as a caculated dimension I can see the right month year combination for every row...The problem comes when I try to use it in the expression that recovers the value for every product. Like this:

if (Type <> 'A', $ (v.KPI.Current.Depreciation.Units), if (Type = 'A', Sum ({<[Data type] = {'4'}, [MonthYearText] = { "Month (Max ({<[Units] = {'> 0'}>} YearMonthNum)) & '' & Year (Max ({<[Units] = {'> 0'}>} YearMonthNum))"}, Vision = {'Contract'}>} [Actual units amortization])))

I can't get any answer

[stigchel]

Yes well, if this is already a calculated dimension, you don't need it in the if(Type='A' part of your expression. I don't know what's in v.KPI.Current.Depreciation.Units and how your If(Type<>'A' relates to your calculated dimension. Try to break it down in steps and see what does not work e.g. leave out the if(type for both cases.

I'm currently out of office and away from my qv install, so you might have to wait a while for an answer from me. Others hopefully help in the meantime.

[trdandamudi]

Give a try on the below:

if (Type <> 'A', $ (v.KPI.Current.Depreciation.Units), if (Type = 'A', Sum ({<[Data type] = {'4'}, [MonthYearText] = { "=Month (Max ({<[Units] = {'> 0'}>} YearMonthNum)) & '' & Year (Max ({<[Units] = {'> 0'}>} YearMonthNum))"}, Vision = {'Contract'}>} [Actual units amortization])))

[juan-carlos]

Thanks a lot for your help Piet

[juan-carlos]

Thanks a lot fot you answer Thirumala.

Here my results.

at column marked with a number 1 I just used the Month year determination that you suggested me that works perfectly

Month (Max ({<[Unidades reales amortizacion] = {'> 0'}>} YearMonthNum)) & ' ' & Year (Max ({<[Unidades reales amortizacion] = {'> 0'}>} YearMonthNum))

The problema comes when I used the whole expresión

if(Tipo<>'A',$(v.KPI.Current.Depreciation.Units),if(Tipo='A',Sum({<[Data type]={'4'},[MonthYearText]=

{ "=Month (Max ({<[Unidades reales amortizacion] = {'> 0'}>} YearMonthNum)) & ' ' & Year (Max ({<[Unidades reales amortizacion] = {'> 0'}>} YearMonthNum))"},Vision = {'Contract'}>}[Unidades reales amortizacion])))

Look, when is used here, the expresion before doesn't return any combination of month year....just the expresión in text format.

That's my common problema...If I put the month year expresión in a varaible it does not return anything to me and the only way that it works is that it modifies my variable vG.App.Last.Amortization.YearMonthText and adds it = at the beginning

= Month (Max ({<[Units] = {'> 0'}>} YearMonthNum)) & '' & Year (Max ({<[Units] = {'> 0'}>} YearMonthNum))

The problem is that then only the highest date of all movements is calculated and the products whose amortization date is lower than the highest date of the whole set do not appear.

[trdandamudi]

It will be helpful if you can share a sample file.

[juan-carlos]

Thanks a lot Thirumala.for spendi!ng some time to help me. How can I attach my sample to the message? I can't see the way to do it

[trdandamudi]

Please follow below link to upload the file...

Uploading a Sample