topic Re: set analysis formula question in QlikView

set analysis formula question

Fri, 28 Feb 2014 18:38:22 GMT

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

Re: set analysis formula question

whiteline — Fri, 28 Feb 2014 18:46:47 GMT

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.

Re: set analysis formula question

Fri, 28 Feb 2014 19:18:51 GMT

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.

Re: set analysis formula question

whiteline — Fri, 28 Feb 2014 19:21:33 GMT

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

Re: set analysis formula question

Fri, 28 Feb 2014 19:27:45 GMT

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.

Re: set analysis formula question

whiteline — Fri, 28 Feb 2014 20:09:39 GMT

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

Re: set analysis formula question

Fri, 28 Feb 2014 20:25:12 GMT

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?

Re: set analysis formula question

whiteline — Fri, 28 Feb 2014 20:43:34 GMT

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.

Re: set analysis formula question

Fri, 28 Feb 2014 21:08:59 GMT

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.

Re: set analysis formula question

whiteline — Fri, 28 Feb 2014 21:14:57 GMT

Yes, the are hardcoded.