Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist
Specialist

From if statement in table to measure

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.

Superartikel Filter 2.PNG

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:

Superartikel Filter 3.PNG

What I want to achieve is that I can make the Sum(# Net Amount) based on this filterting.

Superartikel Filter 1.PNG

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.

1 Solution

Accepted Solutions
Highlighted

Re: From if statement in table to measure

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]))

View solution in original post

4 Replies
Highlighted

Re: From if statement in table to measure

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]))

Highlighted
Specialist
Specialist

Re: From if statement in table to measure

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?

Highlighted

Re: From if statement in table to measure

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]))

View solution in original post

Highlighted
Specialist
Specialist

Re: From if statement in table to measure

Ah, I forgot the Only(). Should've thought of that.

The measure seems to work perfectly.

Thanks for the quick replies, Sunny!