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: 
Not applicable

FirstSortedValue- Null Problem

Hi Friends,

I have requirement to show minimum month totals for each customer. To get this i used firstfortedvalue function but its give null value for one customer who has multiple records for specific month. how can i avoide null and show proper value?

I appriciate your help in advacne.

Original Data Set:

 

[CustID,MonthYear,Quantity
112,Feb-12-01,2
112,Feb-12-01,3
112,Feb-12-02,5
111,Mar-12-01,10
114,Apr-12-01,10
117,Mar-12-01,10
111,Apr-12-01,20
112,Apr-12-01,20
121,Jan-12-01,10]

;

Expected Output:

Cust IDMinimum MonthMonth Quantity
112Feb-1210
111Mar-1210
114Apr-1210
117Mar-1210
121Jan-1210

Output i am gettign using firstsortedvalue function is

 

CustID

 

Min Month

 

Min Month Quantity

 

Jan 2012

 

10

 

111

 

Mar 2012

 

10

 

112

 

Feb 2012

 

-

 

114

 

Apr 2012

 

10

 

117

 

Mar 2012

 

10

 

121

 

Jan 2012

 

10

Please find the attached qvw file. I dont want to group by the data at monthly level as i need to use daily level data for other reports.if you need more details you can find here http://community.qlik.com/message/310168#310168

2 Replies
rbecher
MVP
MVP

Hi,

you can try: FirstSortedValue(DISTINCT Quantity,Monthstart,1)

- Ralf

Astrato.io Head of R&D
swuehl
MVP
MVP

If you want to aggregate your monthly data, you can use advanced aggregation, try something like

=FirstSortedValue(aggr(sum(Quantity),CustID,Monthstart),aggr(Monthstart,CustID,Monthstart))