Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
juan-carlos
Contributor II
Contributor II

Problems with the Last Transaction Date

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

18 Replies
stigchel
Partner - Master
Partner - Master

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
Contributor II
Contributor II
Author

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
Partner - Master
Partner - Master

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
Master II
Master II

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
Contributor II
Contributor II
Author

Thanks a lot for your help Piet

juan-carlos
Contributor II
Contributor II
Author

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

Qlik.JPG

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
Master II
Master II

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

juan-carlos
Contributor II
Contributor II
Author

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
Master II
Master II

Please follow below link to upload the file...

Uploading a Sample