Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

MindaugasBacius
Valued Contributor II

Aggr or FirstSortedValue

Hello

I need some help.

I need to sum quantity according to the max date in the month:

Screenshot_1.jpg

The result:

Year month, Sum

2017-02, 3

2017-03, 5

Thank you for your help!

Tags (2)
1 Solution

Accepted Solutions

Re: Aggr or FirstSortedValue

Try this:

FirstSortedValue(Aggr(Sum(Quantity), Date), -Date)

Capture.PNG

8 Replies

Re: Aggr or FirstSortedValue

Try this:

FirstSortedValue(Aggr(Sum(Quantity), Date), -Date)

Capture.PNG

aarkay29
Valued Contributor

Re: Aggr or FirstSortedValue

Try this,

Sum (Aggr (Firstsortedvalue (quantity,-Date),Year Month))

MindaugasBacius
Valued Contributor II

Re: Aggr or FirstSortedValue

And how that would work in case we have two dimensions Year month, Product?

Re: Aggr or FirstSortedValue

Difficult to say without looking, but may be this:

FirstSortedValue(Aggr(Sum(Quantity), Product, Date), -Aggr(Date, Product, Date))

MindaugasBacius
Valued Contributor II

Re: Aggr or FirstSortedValue

Thanks once again!

Not applicable

Re: Aggr or FirstSortedValue

Hi Sunny,

many thanks for your suggestion: it works very well! Instead, I have a problem when I try to apply the expression to calculate the previous year value.

In fact, it seems that the function FirstSortedValue is not able to consider a set analysis expression:

=sum(aggr(FirstSortedValue({<Date={'<=$(=max(Date)-100)>=$(=min(Date)-100)'}>}aggr(Costs,Project,Date),-aggr(Date,Project,Date)),Project))

 

The date format is YYYYMM (i.e. 201701)

Am I wrong? How can I manage this issue?

Best,

Matteo

Re: Aggr or FirstSortedValue

Not entirely sure, but try this

=Sum({<Date = {"$(='<=' & Date(Max(Date)-100, 'YYYYMM') & '>=' & Date(Min(Date)-100, 'YYYYMM'))"}>} Aggr(FirstSortedValue({<Date = {"$(='<=' & Date(Max(Date)-100, 'YYYYMM') & '>=' & Date(Min(Date)-100, 'YYYYMM'))"}>} Aggr(Only({<Date = {"$(='<=' & Date(Max(Date)-100, 'YYYYMM') & '>=' & Date(Min(Date)-100, 'YYYYMM'))"}>} Costs), Project, Date), -Aggr(Only({<Date = {"$(='<=' & Date(Max(Date)-100, 'YYYYMM') & '>=' & Date(Min(Date)-100, 'YYYYMM'))"}>} Date), Project, Date)), Project))

Not applicable

Re: Aggr or FirstSortedValue

Hi Sunny,

I implemented this formula because yours seems not to work in my dashboard:

=sum(aggr(FirstSortedValue({<Date={'<=$(=max(Date)-100)>=$(=min(Date)-100)'}>}aggr(only({<Date={'<=$(=max(Date)-100)>=$(=min(Date)-100)'}>} Costs),Project,Date),-aggr(only({<Date={'<=$(=max(Date)-100)>=$(=min(Date)-100)'}>}Date),Project,Date)),Project))

This one is fine on a local test qvw with a limited amount of data.

My current problem is to insert into this expression the sum function regarding Costs while keeping the set analysis for the previous year.

Do you have any idea on that?

Many thanks

Community Browser