topic Re: Indirect set Analysis in QlikView

Indirect set Analysis

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Indirect set Analysis

vishsaggi — Wed, 22 Feb 2017 17:53:29 GMT

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?

Re: Indirect set Analysis

sunny_talwar — Wed, 22 Feb 2017 17:56:43 GMT

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

Re: Indirect set Analysis

Anonymous — Wed, 22 Feb 2017 18:14:21 GMT

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

Re: Indirect set Analysis

Fri, 24 Feb 2017 08:42:35 GMT

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.

Re: Indirect set Analysis

sunny_talwar — Fri, 24 Feb 2017 10:55:05 GMT

Did you have the same issue with this expression?

May be use this for 1st expression:

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

Re: Indirect set Analysis

Anonymous — Fri, 24 Feb 2017 17:04:10 GMT

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