topic Re: Problem with Set analysis in QlikView

Problem with Set analysis

Mon, 11 Feb 2013 15:18:40 GMT

Hi Friends,

I have a requirement to show minimum month data by customer. I tried set analysis to do this but its not working as expected. Could anyone please help me on this issue.

I greatly appreciate your help in advance

My original data set is

Cust ID	MonthYear	Quantity
112	Feb-12	10
111	Mar-12	10
114	Apr-12	10
117	Mar-12	10
111	Apr-12	20
112	Apr-12	20
121	Jan-12	10

Correct 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

Set analysis i used to restrict the min month in expression is

sum

({<MonthYear={'$(=MinString(MonthYear))'}>}Quantity

)

output i am getting is

CustID	Min Month	Min Month Quantity
	Apr-12	50
111	Apr-12	20
112	Apr-12	20
114	Apr-12	10
117	Mar-12	0
121	Jan-12	0

I have attached my qvw file as well.

Re: Problem with Set analysis

manideep78 — Mon, 11 Feb 2013 15:24:40 GMT

Try this,

add CUSTID and Minimum Month as dimensions and write expression as

=min(Month Quantity)

Re: Problem with Set analysis

vivientexier — Mon, 11 Feb 2013 15:24:42 GMT

In this case, "MonthYear" should not be a dimension ; it is the result of an expression.

Re: Problem with Set analysis

vivientexier — Mon, 11 Feb 2013 15:29:38 GMT

Why do you use MinString()'s function ? It is easier to play with date after you transformed them to "numbers".

Re: Problem with Set analysis

Mon, 11 Feb 2013 15:29:50 GMT

Yes , that is correct i kept "MonthYear" as an expression like this to find min month by customer"

MinString(MonthYear

)

i cant put month year as dimension becasue i have to show total volume(for all months) also in another expression.

Re: Problem with Set analysis

Mon, 11 Feb 2013 15:29:57 GMT

Yes , that is correct i kept "MonthYear" as an expression like this to find min month by customer"

MinString(MonthYear

)

i cant put month year as dimension becasue i have to show total volume(for all months) also in another expression.

Re: Problem with Set analysis

Mon, 11 Feb 2013 15:34:31 GMT

Thanks vivien for response,

If i use just min function its giving nothing in that expression. i agree with you ,minstring() is also worng because that it considering month as text.any ideal how to fix that issue

Re: Problem with Set analysis

Mon, 11 Feb 2013 15:37:15 GMT

hai pls find the attachment

Re: Problem with Set analysis

swuehl — Mon, 11 Feb 2013 15:37:57 GMT

Set analysis is not the way to go here, you'll need to evaluate your min Date per CustID.

- Read your MonthYear in as date:

Date#(MonthYear,'MMM-YY') as Monthstart

- Create a chart with dimension CustID and two expressions:

=Monthname(min(Monthstart))

=FirstSortedValue( Quantity, Monthstart)

Re: Problem with Set analysis

Mon, 11 Feb 2013 15:53:02 GMT

Swuehi,

Thank you, But in my actual requirement i need to put some product restrictions so i tried below set analysis but its not working

FirstSortedValue(sum({<$(=$(v_Prod_Select))$(v_Rx2)),MonthYear)

Re: Problem with Set analysis

swuehl — Mon, 11 Feb 2013 16:10:35 GMT

Hard to say what you want to do here, you'll need at least to post your variable definitions, too. At best, post a small sample file that demonstrate your issue.

One thing I already noticed, you are using an aggregation function inside another aggregation function (sum() inside FirstSortedValue() ). This is not allowed without using advanced aggregation (aggr() function).

Not sure if you need to use sum() at all, you could potentially just use a set expression with the FirstSortedValue() function.

Re: Problem with Set analysis

Mon, 11 Feb 2013 18:29:48 GMT

Thanks you, i made it work using firstsortedvalue function. But the function is returning null when there are multiple records for specific month. How can i avoid this null and group to monthly level in frontend?

Please look at the attached example. I have added extra records to customer 112 and spitted feb-12 quantity into multiple dates. In front end its showing null for customer 112. Is there any way to show the proper total(10) for feb-12 without grouping data at monthly level?

Re: Problem with Set analysis

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

Try something like

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