Set Analysis to check previous values

[Anonymous]

Hi,

Need some help with my set analysis logic.

1. I'm trying to determine if a customer is a new customer based on this condition:

a) customer has no transaction from the last 6 months from selected period. Example, Customer ABC; select Jun2015. Check from Dec2014 to May2015 for any Sales for this customer. If no sales, then sum the sales in Jun 2015 else return 0.

sum({$< _OH_Code = {"=sum({$<MC_Period=, MC_Year=, MC_Quarter=, MC_Week=, MC_Date = {'>$(=MonthEnd(addmonths(max(MC_Date),-7)))<=$(=MonthEnd(addmonths(max(MC_Date),-1)))'}>}GP) = 0"} >} GP)

However, nothing is being summed up. What is wrong with the above syntax?

Any feedback much appreciated. thanks!

[ankitaag]

if(sum({<MC_Date ={'>$(=MonthEnd(addmonths(max(MC_Date),-7)))<=$(=MonthEnd(addmonths(max(MC_Date),-1)))'}>}GP)=0,sum(GP),0)

[Anonymous]

thanks, Ankita. Is there any way to do purely using Set Analysis?

This is because i need to use this formula to then calculate if FS_TL_ID exists in the past 6 months.

sum({$<MC_Date=,

FS_TL_ID= {"=sum({$<MC_Period=, MC_Year=, MC_Quarter=, MC_Week=, MC_Date = {'>$(=MonthEnd(addmonths(max(MC_Date),-7)))<=$(=MonthEnd(addmonths(max(MC_Date),-1)))'}>}GP) = 0"}>}GP)

i have tried using IF conditions, but it will not work without the FS_TL_ID added as a dimension.

regards

Derrell

[maxgro]

for new and lost customer there is a detailed post here (add dot)

poverconsulting com/2014/03/04/set-analysis-nested-advanced-search/

[ToniKautto]

The main reason to the set expression not working as you intended is likely that the syntax is incorrect.

The first thing that I notice in your example is that you are trying to apply an expression as the set modifier, and not the calculated result from the expression.

In the below example the the set modifier applies a selection in field _OH_Code that is equal to the text "=sum(GP) = 0".

sum({$<_OH_Code = {"=sum(GP) = 0"} >} GP)

In the below example the expression is calculated and the result is expanded in to the expression through the dollar expansion. The equal sign in the dollar expansion means that QlikView will  evaluate the following expression. 

sum({$<_OH_Code = {"$(=sum(GP) = 0)"} >} GP)

The second thing that I think will could cause you problems in this example is the MonthEnd() expansion. Notice that this will also be calculated as a expression total, and will also be evaluated before the chart expression is calculated.

$(=MonthEnd(addmonths(max(MC_Date),-1)))

My suggestion is that you create four variables in the application to represent your calculations, and then evaluate if the results are expected in the context of expanding them in the chart expression. I think this will give you a better overview of what is actually being used int he chart expression.

Variabel1 = MonthEnd(addmonths(max(MC_Date),-7))

Variable2 = MonthEnd(addmonths(max(MC_Date),-1))

Variable3 = sum({$<MC_Period=, MC_Year=, MC_Quarter=, MC_Week=, MC_Date = {">$(Variable1)<=$(Variable2)"}>}GP)

Variable4 = $(Variable3)=0

This will leave you with a chart expression that only contains the Variable4, and it is not easier to see that the same expression will be used on each dimension value in the chart.

Chart Expressions = sum({$<_OH_Code = {"$(Variable4)"} >} GP)

To summarize the set expression can not be dynamically altered from row to row in your chart. If this is what you are trying to do, I hope the above example helps to explain how the setexpression and dollar expansion works.

[Anonymous]

Hi Toni,

Thanks for helping,

I've tried to apply your suggestions and i did noticed that Variable3 may have a syntax error even though QV expression says "OK".

should the variable3 expression be like this?

=sum({$<MC_Period=, MC_Year=, MC_Quarter=, MC_Week=, MC_Date = {">$(Variable1)<=$(Variable2)"}>}GP)

regards

Derrell

[ToniKautto]

Correct. Sorry about the copy paste mistake I made there, it has been corrected in the previous post.

[Anonymous]

Thanks, Toni. i am still struggling...

I've got this far -> Chart Expressions = sum({$<_OH_Code = {"$(Variable4)"} >} GP)

it looks like it is pulling all the dates correctly; but in the table i get a '-'

it looks like it is unable to display the expression. any ideas why this could be the reason?

[ToniKautto]

Your variables are no defined as below.

In your initial question you referred to Jun 2015 being the selected MC_Period, which will give you the following result in the variables.

Effectively this means that the expression in Variable3 will look be a set expression limited by the Variable1 and Varibale2 dates, just as shown below. If you place this expression in your straight table you will see that the ABC line returns 0, this is an indication of the ABC row having no records or a zero value record.

=Sum({$<MC_Period=, MC_Year=, MC_Quarter=, MC_Week=, MC_Date = {">30/11/2014<=31/05/2015"}>}GP)

If you instead use Count() you will be able to confirm that there is no record associated with the set expression.

=Count({$<MC_Period=, MC_Year=, MC_Quarter=, MC_Week=, MC_Date = {">30/11/2014<=31/05/2015"}>}GP)

I suspect that a bigger problem might be that Variable4 will be based on the expression total of Variable3, meaning that Variable4 willbe calculated as 345=0 at all times and not as a row by row value. Correct me if I am wrong, but I think you are trying to compare the ABC value with 0 and not the expression total of 345?

[Anonymous]

Hi Toni,

So the idea is when ABC has a value of 345, I don't have to sum up the GP in Jun2015 for ABC.

If ABC has 0 value in Variable3, then I should sum the GP in the selected month.

I am not sure if assigning the calculation to _OH_Code will correctly return the result i want.

Similarly, if I can get this working, i will then try to do a similar check for FS_TL_ID (If no values in Variable3, then sum all GP for FS_TL_ID).

Happy to hear any better way of doing this. I'm just stuck