Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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

Tags (2)
1 Solution

Accepted Solutions
Not applicable

Re: set analysis formula question

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. 

9 Replies
whiteline
Honored Contributor II

Re: set analysis formula question

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

Re: set analysis formula question

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
Honored Contributor II

Re: set analysis formula question

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

Not applicable

Re: set analysis formula question

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
Honored Contributor II

Re: set analysis formula question

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

Not applicable

Re: set analysis formula question

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
Honored Contributor II

Re: set analysis formula question

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

Re: set analysis formula question

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
Honored Contributor II

Re: set analysis formula question

Yes, the are hardcoded.

Community Browser