Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

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.

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Problem with Set analysis

Try something like

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

12 Replies
manideep78
Valued Contributor

Re: Problem with Set analysis

Try this,

add CUSTID and Minimum Month as dimensions and write expression as

=min(Month Quantity)

vivientexier
Contributor II

Re: Problem with Set analysis

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

vivientexier
Contributor II

Re: Problem with Set analysis

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

Not applicable

Re: Problem with Set analysis

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

Re: Problem with Set analysis

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

Re: Problem with Set analysis

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

Re: Problem with Set analysis

hai pls find the attachment

MVP
MVP

Re: Problem with Set analysis

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

Re: Problem with Set analysis

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)

Community Browser