Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am looking for set analysis help on the data below. Basically I need to count distinct groups that had less revenue in the current month than in the previous month. (in the example below group 3 and 5 had less revenue in the current month than the previous).
DATA: | |||
DIVISION | GROUP | YEARMONTH | REVENUE |
A | 1 | 201301 | 90 |
A | 1 | 201302 | 100 |
A | 2 | 201301 | 110 |
A | 2 | 201302 | 120 |
A | 3 | 201301 | 150 |
A | 3 | 201302 | 100 |
B | 4 | 201301 | 100 |
B | 4 | 201302 | 125 |
B | 5 | 201301 | 140 |
B | 5 | 201302 | 100 |
RESULT: | |||
Division | YEARMONTH | % of Groups with Less Revenue then Prior Month | |
A | 201302 | 33% | |
B | 201302 | 50% |
hi,
create two dynamic variables for Current and Previous Year Month
In the example:
vCurrentYearMonth= 201302
vCurrentYearMonth= 201301
and then try this set analysys=
=Count(DISTINCT
if( only({<YEARMONTH={$(vCurrentYearMonth)}>}REVENUE ) < only({<YEARMONTH={$(vPrevYearMonth)}>}REVENUE ),
GROUP))
Hope this helps.
MC
See the attached file.
Basically a chart with Division as your dimension and the follwoing expression. I've added a master calendar to make this easier and changed your dates so they actually are considered "This Month" and "Last Month"
=count(distinct AGGR(
If(1 - (sum({$<DateRange={'Last Month'}>}REVENUE) / sum({$<DateRange={'This Month'}>}REVENUE))
<1,DIVISION)
,DIVISION)
)
/
count(DISTINCT GROUP)
Unfortunately I need this by month for the past 3 years so the concept of this month versus last month doesn't work in that scenario. This is probably a better depiction of needing to do this for multiple months...
DATA: | |||
DIVISION | GROUP | YEARMONTH | REVENUE |
A | 1 | 201301 | 90 |
A | 1 | 201302 | 100 |
A | 1 | 201303 | 100 |
A | 2 | 201301 | 110 |
A | 2 | 201302 | 120 |
A | 2 | 201303 | 90 |
A | 3 | 201301 | 150 |
A | 3 | 201302 | 100 |
A | 3 | 201303 | 80 |
B | 4 | 201301 | 100 |
B | 4 | 201302 | 125 |
B | 4 | 201303 | 100 |
B | 5 | 201301 | 140 |
B | 5 | 201302 | 100 |
B | 5 | 201303 | 80 |
RESULT: | |||
Division | YEARMONTH | % of Groups with Less Revenue then Prior Month | |
A | 201302 | 33% | |
B | 201302 | 50% | |
A | 201303 | 66% | |
B | 201303 | 100% |
If YEARMONTH is required as a dimenion I don't see how this can work with set analysis. Maybe someone else will help you out on this.
Another approach is to do it in the script. You can load your data by month and compare the current row to the previous (month) using the previous() funciton.
Thanks Mark - I actually was doing this in the script before using previous. Where I ran intop problems is there can be multiple revenue lines for a group so it looks at only the previous record line but there maybe be a couple of others also. Couldn't figure out a way around that.
One question for you - I struggle alot with aggregation (and advanced set analysis) in objects. Is it safe to say that aggregation and Set analysis should not include the dimensions in your object?
See attached example.
And set analysis creates one set for the entire chart, not per row. That's why using a field both as a chart dimension and in the set modifier often doesn't do what you would like.
Thanks! I am going to to try to incorporate this into our model.
Gysbert has a good article on this:http://community.qlik.com/docs/DOC-4252
I should have read this first maybe