Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

set analysis formula question

Hi,

I'm a new user to Qlikview with little experience in writing formulas, so any help is much appreciated.  Here is what I'm trying to do:

For a select population of customers, return those that have had no sales in the past 2 quarters, but have had sales in the 4 quarters prior.  Eventually, I would like to show what those sales were in the prior 4 quarters.

I believe I can get the list of customers a roundabout way by the following formula, with customer name as the dimension:

=Sum({$<CUST_REGION={'A'},QTR_ID={'201302','201303','201304','201401'}>}SALES)-Sum({$<CUST_REGION={'A'},QTR_ID={'201302','201303'}>}SALES)

If a customer comes back with zero or negative sales, then they had no activity in the recent two quarters.

However, I think there is an easier/more effective way using the p() or e() functions, but I just can't get it to work.

Thanks - Matt

1 Solution

Accepted Solutions
Not applicable
Author

Thank you for the quick reply!  I changed the formula to match this, but I'm coming back with no data.  I've triple-checked the syntax, and it aligns with what you sent, so I must be missing something else.  I'm on v10, if that has any bearing. 

View solution in original post

9 Replies
whiteline
Master II
Master II

Hi.

There is easier way to handle this case.

You can keep in set only those customers that match your criteria directly:

Sum({$<CUST_ID={"=Sum({$<CUST_REGION={'A'},QTR_ID={'201302','201303'}>}SALES)>0 and  Sum({$<CUST_REGION={'A'},QTR_ID={'201304','201401'}>}SALES)=0"}>} SALES)

With this you can get just the sum of sales of those customers.

Not applicable
Author

Thank you for the quick reply!  I changed the formula to match this, but I'm coming back with no data.  I've triple-checked the syntax, and it aligns with what you sent, so I must be missing something else.  I'm on v10, if that has any bearing. 

whiteline
Master II
Master II

Did you notice that I've just guess that you have CUST_ID field as customer ids ?

Not applicable
Author

Yes - I switched it to customer_name (which is the field name).  Customer_name is the only dimension I'm using, and the only expression would be the one you provided. 

whiteline
Master II
Master II

Could you upload the sample qvw (with scrambled data) ?

Not applicable
Author

Ok - I got it to work simply by exiting QV and coming back in. Thank you again - and hopefully I can bother you with one other question.  I may decide to further limit this group by sales volume and transaction quantity.  I imagine I can filter sales volume by changing the "0" to a new number.  If I want to limit by transaction quantity, would I just add another "and" condition, and substitute transaction count for sales? 

whiteline
Master II
Master II

Definitely yes.

Although, you could think of calculating some fields/flags in script to simplify the set expressions or if the condition is not supposed to be dynamic relative to user selections.

Not applicable
Author

And that's one of many areas where I need to learn more; as in: I was specifying the time conditions, and ideally it doesn't matter what the application selections are, it will always give me the sales for just those quarters I defined, unless I change the definition.

whiteline
Master II
Master II

Yes, the are hardcoded.