Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Try something like
=FirstSortedValue(aggr(sum(Quantity),CustID,Monthstart),aggr(Monthstart,CustID,Monthstart))
Try this,
add CUSTID and Minimum Month as dimensions and write expression as
=min(Month Quantity)
In this case, "MonthYear" should not be a dimension ; it is the result of an expression.
Why do you use MinString()'s function ? It is easier to play with date after you transformed them to "numbers".
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.
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.
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
hai pls find the attachment
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
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)