topic Re: Problems with the Last Transaction Date in QlikView

Problems with the Last Transaction Date

juan-carlos — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Problems with the Last Transaction Date

stigchel — Thu, 20 Sep 2018 16:06:50 GMT

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?

Re: Problems with the Last Transaction Date

juan-carlos — Fri, 21 Sep 2018 07:43:29 GMT

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

Re: Problems with the Last Transaction Date

stigchel — Fri, 21 Sep 2018 18:55:06 GMT

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.

Re: Problems with the Last Transaction Date

trdandamudi — Fri, 21 Sep 2018 19:14:09 GMT

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

Re: Problems with the Last Transaction Date

juan-carlos — Tue, 25 Sep 2018 07:45:28 GMT

Thanks a lot for your help Piet

Re: Problems with the Last Transaction Date

juan-carlos — Tue, 25 Sep 2018 08:07:17 GMT

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.

Re: Problems with the Last Transaction Date

trdandamudi — Tue, 25 Sep 2018 15:20:56 GMT

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

Re: Problems with the Last Transaction Date

juan-carlos — Tue, 02 Oct 2018 15:17:14 GMT

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

Re: Problems with the Last Transaction Date

trdandamudi — Tue, 02 Oct 2018 15:49:31 GMT

Please follow below link to upload the file...

Uploading a Sample

Re: Problems with the Last Transaction Date

juan-carlos — Wed, 03 Oct 2018 06:48:07 GMT

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

Re: Problems with the Last Transaction Date

trdandamudi — Wed, 03 Oct 2018 11:36:09 GMT

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

Re: Problems with the Last Transaction Date

juan-carlos — Thu, 04 Oct 2018 14:56:21 GMT

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

Re: Problems with the Last Transaction Date

trdandamudi — Thu, 04 Oct 2018 15:33:59 GMT

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

Re: Problems with the Last Transaction Date

juan-carlos — Fri, 05 Oct 2018 06:36:59 GMT

Morning Thirumala,

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

Re: Problems with the Last Transaction Date

trdandamudi — Fri, 05 Oct 2018 11:17:23 GMT

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

Re: Problems with the Last Transaction Date

juan-carlos — Fri, 05 Oct 2018 11:33:02 GMT

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

Re: Problems with the Last Transaction Date

trdandamudi — Fri, 05 Oct 2018 12:46:57 GMT

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])),'.','')

Re: Problems with the Last Transaction Date

juan-carlos — Mon, 08 Oct 2018 09:31:16 GMT

Hi Thirumala,

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

so calculation should looks like this: