Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey all,
I recently encountered a problem involving a lot of filtering through set analysis.
To make things easier, I simplified the filtering based on priority level.
A little clarification:
A client has 10 "super articles". There's a seperate view for each article.
Now, the client sells items. And items are built up out of articles, including super articles.
The problem was: under which super article do we show an item when it's built up out of more than one super article.
I made 4 priority levels. In the application, the field is called "Filter weight".
Now based on those levels, I can decide which item is shown under which super article.
The super articles with priority(Filter weight) = 1 don't require extra filtering as they need to be shown under their respective super article.
When an item is built up out of super articles with priority 2, 3 or 4, I do require extra filtering.
The extra filtering is done as such:
If(not WildMatch(Aggr(Concat([Filter weight]),Item),'*1*')>0,
If(WildMatch(Aggr(Concat([Filter weight]),Item),'*2*')>0,Item)
)
This is for a super article which belongs to priority level 2.
If(not WildMatch(Aggr(Concat([Filter weight]),Item),'*1*')>0 AND not WildMatch(Aggr(Concat([Filter weight]),Item),'*2*')>0,
If(WildMatch(Aggr(Concat([Filter weight]),Item),'*3*')>0,Item)
)
This is for a super article which belongs to priority level 3.
The filter is used on the dimension Item.
In my tables there's no problem using this type of filtering for the items I don't want to see.
However, I don't seem to be able to incorporate this in a measure NOT in a table, which is due to there not being a structure anymore. I tried to solve this with the Aggr function, but it seems I can't figure it out.
An example:
For Order 220150, we've got Item 40. Item 40 is built up out a number of articles, of which 3 super articles which belong to different priority levels.
Through the filter for a super article which belongs to priority level 2, I can determine it is shown under the correct super article.
The filter is done based on a Concat of the filter weight of all the articles(products) under the item:
What I want to achieve is that I can make the Sum(# Net Amount) based on this filterting.
Which works fine in the table above, as we got a structure of Document Number ==> Item ==> Product.
But when I want to make a measure for the Sum(# Net Amount) for a super article belonging to priority level 2 in a KPI, I can't seem to get it to work through an Aggr function.
Is it possible to edit the filtering so it's usable in a measure outside of a table?
P.S.: As attachement, I've put an Excel with the data shown in the screenshots.
It is possible.... replace {<SetAnalysis>} in the following places
Sum({<SetAnalysis>}Aggr(If(Only({<SetAnalysis>}[Filter Weight]) = Min({<SetAnalysis>}TOTAL <[Document Number], Item> [Filter Weight]), Sum({<SetAnalysis>}[# Net Amount])), [Document Number], Item, [Product Group]))
Not entirely sure what you have, but see if this works
Sum(Aggr(If([Filter Weight] = Min(TOTAL <[Document Number], Item> [Filter Weight]), Sum([# Net Amount])), [Document Number], Item, [Product Group]))
Hi Sunny
Sorry for the late response.
In general the measure seems to work.
If I'd use an extra set analysis with this measure, where exactly would I fit it in?
Or is it even possible to do that?
It is possible.... replace {<SetAnalysis>} in the following places
Sum({<SetAnalysis>}Aggr(If(Only({<SetAnalysis>}[Filter Weight]) = Min({<SetAnalysis>}TOTAL <[Document Number], Item> [Filter Weight]), Sum({<SetAnalysis>}[# Net Amount])), [Document Number], Item, [Product Group]))
Ah, I forgot the Only(). Should've thought of that.
The measure seems to work perfectly.
Thanks for the quick replies, Sunny!