Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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.
Did you notice that I've just guess that you have CUST_ID field as customer ids ?
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.
Could you upload the sample qvw (with scrambled data) ?
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?
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.
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.
Yes, the are hardcoded.