Indirect set Analysis

[#1]

Good morning,

I am writing to submit you a question that I can't solve:

I have to compare values from different periods: selecting a year - month I would be able to have the sum of the previous year and the same month.

The problem is that the previous year can have several periods with the same year-month field, so I need that QlikView automatically select the one with the highest date of processing.

I attach the file where, for some selections (for ex. 2017-> 12 or 2017-> 08 exists more 2016-> 12 or 2016 -> 08.

The correct result for 2017-08 is:

Name  AcMc ApMp

Alfa      240      230

Beta    230      220

Thanks in advance.

Ivan

[vishsaggi]

Why you should get 230 and 220 in your example? For year 2016 and Month 08 the values will sum up like as shown, so if you see in your Name of Period you have two possible periods with different amounts?

[sunny_talwar]

May be this:

FirstSortedValue({1<[Name of period] = {'$(ApMc)*'}>}Aggr(Sum({1<[Name of period] = {'$(ApMc)*'}>}Amount), [Date of processing], Name), -Aggr(Only({1<[Name of period] = {'$(ApMc)*'}>}[Date of processing]), [Date of processing], Name))

[Anonymous]

LOAD * INLINE [
year, month, name, date of processing, name of period, amount
2016, 8, Alfa, 22.02.2017, 2016-08-AA02-Testo libero, 230
2017, 8, Alfa, 22.01.2017, 2017-08-AA01-Testo libero, 240
2017, 12, Alfa, 12.04.2017, 2017-12-AA04-Testo libero, 250
2016, 12, Alfa, 05.01.2017, 2016-12-AA01-Testo libero, 310
2016, 11, Alfa, 01.01.2017, 2016-11-AA01-Testo libero, 220
2016, 11, Alfa, 20.01.2017, 2016-11-AA01-Testo libero, 230
2016, 8, Alfa, 01.08.2016, 2016-08-AA08-Testo libero, 210
2016, 8, Beta, 22.02.2017, 2016-08-AA02-Testo libero, 220
2017, 8, Beta, 22.01.2017, 2017-08-AA01-Testo libero, 230
2017, 9, Beta, 12.01.2016, 2017-09-AA01-Testo libero, 240
2016, 12, Beta, 05.01.2017, 2016-12-AA01-Testo libero, 300
2016, 11, Beta, 01.01.2017, 2016-11-AA01-Testo libero, 210
2016, 11, Beta, 20.01.2017, 2016-11-AA01-Testo libero, 220
2016, 8, Beta, 01.08.2016, 2016-08-AA08-Testo libero, 200
2016, 12, Alfa, 04.01.2017, 2016-12-AA01-Testo libero, 299
2016, 12, Beta, 04.01.2017, 2016-12-AA01-Testo libero, 289
];

expression1:

FirstSortedValue(distinct amount,-[date of processing])

expression2:

FirstSortedValue(distinct {<year={"$(=max(year)-1)"},month={"$(=max(month))"}>} amount,-[date of processing])

[#5]

Dear Robin,

it works fine.

Only a question,

This expression shows only the first value. If I have 2 or more values that I want to sum (like a pivot) with the same rules (only the ones with the highest date of processing) . It doesn't run!

Is there anything else I can adopt if I want to use a pivot table?

thanks.

[sunny_talwar]

Did you have the same issue with this expression?

FirstSortedValue({1<[Name of period] = {'$(ApMc)*'}>}Aggr(Sum({1<[Name of period] = {'$(ApMc)*'}>}Amount), [Date of processing], Name), -Aggr(Only({1<[Name of period] = {'$(ApMc)*'}>}[Date of processing]), [Date of processing], Name))

May be use this for 1st expression:

FirstSortedValue(Aggr(Sum(Amount), [Date of processing], Name), -Aggr(Date of processing], [Date of processing], Name))

[Anonymous]

sorry, I made a mistake, the correct second expression is:

FirstSortedValue(distinct {<year={"$(=max(year)-1)"},month={"$(=max(month))"}>}  aggr(sum({<year={"$(=max(year)-1)"},month={"$(=max(month))"}>} amount),name,[date of processing]),-[date of processing])