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 ???

[juan-carlos]

Morning Thirumala and thanks again for you time...Here my simple:

I have created a screen selection called test where I select product type A & Vision=contract

I have created a variable called vG.App.Last.Amortization.YearMonthText that gets the last month with movement for every product.

and seems to work as you can see at the last column.

The goal is that at column Calculation the system should show the amount of units for the last month with movement (for the first product ID if should be 2). No idea why it doesn't work because the function seems to be correct...Any idea ??

[trdandamudi]

You attached a Qliksense file but I do not have Qliksense on my side. Hope someone will answer this.

Note: You marked my upload link as correct answer and please remove it because it is not the answer and other users might get confused.

Thanks

[juan-carlos]

Hi again Thirumala,

here the simple in Qlikview format. I have created a selection called test. Last colum is the variable that gets the last month with movement (that Works correctly). Calculation should show the value(Unidades) from the last month (4), but is always 0.  Any idea what am I doing wrong??

[trdandamudi]

Is this what you are looking for ?

If the above is correct then you need to just add an equal sign ( = ) in front of your variable as shown below:

=replace(Capitalize(month(Max([YearMonthNum])))&' '&Year(Max([YearMonthNum])),'.','')

When you get a chance please go over this link: The Magic of Variables Hope this helps..

[juan-carlos]

Morning Thirumala,

did it works for you ? I just get this error when I did the changes

[trdandamudi]

Yes, it is working on my side and attached is the file.

[juan-carlos]

So weird...it seems  to work...but unselect the product ID and see what happens.... I already have this issue at the  beggining

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]

Sorry not able to follow... If I unselect the "Product ID" then what is your expected output? Can you share a screen shot of the output please..

When I unselected then the max month is "Jul 2016" and for that month "[Unidades reales amortizacion]" it is 0 (zero) and that is the reason it is showing 0 in "Calculation" field.

Update:

When I am using the following it looks like it is working:

=replace(Capitalize(month(Max({<[Unidades reales amortizacion]-={0}>}[YearMonthNum])))&' '&Year(Max({<[Unidades reales amortizacion]-={0}>}[YearMonthNum])),'.','')

[juan-carlos]

Hi Thirumala,

The idea is getting the units from the last movement of every product,

so calculation should looks like this: