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

Problem with Set analysis

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 IDMonthYearQuantity
112Feb-1210
111Mar-1210
114Apr-1210
117Mar-1210
111Apr-1220
112Apr-1220
121Jan-1210

Correct Output

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

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

 

sum

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

)

output i am getting is

CustIDMin MonthMin Month Quantity
Apr-1250
111Apr-1220
112Apr-1220
114Apr-1210
117Mar-120
121Jan-120

I have attached my qvw file as well.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Try something like

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

View solution in original post

12 Replies
manideep78
Partner - Specialist
Partner - Specialist

Try this,

add CUSTID and Minimum Month as dimensions and write expression as

=min(Month Quantity)

vivientexier
Partner - Creator II
Partner - Creator II

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

vivientexier
Partner - Creator II
Partner - Creator II

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

Not applicable
Author

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.

Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

hai pls find the attachment

swuehl
MVP
MVP

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)

See also attached.

Stefan

Not applicable
Author

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)