FirstSortedValue- Null Problem

Mon, 11 Feb 2013 19:52:21 GMT

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 ID	Minimum Month	Month Quantity
112	Feb-12	10
111	Mar-12	10
114	Apr-12	10
117	Mar-12	10
121	Jan-12	10

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

Re: FirstSortedValue- Null Problem

rbecher — Mon, 11 Feb 2013 20:27:37 GMT

Hi,

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

- Ralf

Re: FirstSortedValue- Null Problem

swuehl — Mon, 11 Feb 2013 21:10:15 GMT

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

topic Re: FirstSortedValue- Null Problem in QlikView

FirstSortedValue- Null Problem

Re: FirstSortedValue- Null Problem

Re: FirstSortedValue- Null Problem