topic Re: Variable value change according to dimension in set analysis in Integration, Extension & APIs

Variable value change according to dimension in set analysis

Anonymous — Fri, 05 Jan 2018 09:50:25 GMT

Hi,

I have Date, Membership signup date ,Customers and orders, I want the output where each customer before signup how many orders or after signup how much orders they are placed.

Each customer have their each signupdate.

For example-

Cust	Date	SignUp date	orders
1	2017-12-30	2017-10-30	0.49145
1	2017-12-12	2017-10-30	0.98633
1	2017-11-19	2017-10-30	0.32968
1	2017-11-16	2017-10-30	0.24649
1	2017-11-06	2017-10-30	0.19674
1	2017-11-06	2017-10-30	0.19695
1	2017-11-06	2017-10-30	0.19768
1	2017-11-06	2017-10-30	0.19798
1	2017-10-30	2017-10-30	0.33333
1	2017-10-07	2017-10-30	0.5
1	2017-03-25	2017-10-30	1
1	2017-02-23	2017-10-30	0

I want output sum(orders) before signup date 2017-10-30 and after signupdate, I have so many customers and having different signup date.

Thanks for help!

Re: Variable value change according to dimension in set analysis

sunny_talwar — Fri, 05 Jan 2018 09:54:10 GMT

May be create a flag in the script and then use the flag to determine before and after?

If(Date > SignUPdate, 'Before', 'After') as BeforeAfterFlag

Now use these expressions

Sum({<BeforeAfterFlag = {'Before'}>} order)

Sum({<BeforeAfterFlag = {'After'}>} order)

Re: Variable value change according to dimension in set analysis

Anonymous — Fri, 05 Jan 2018 10:06:34 GMT

I don't want to change script , there is no way to do on dashboard only.

Re: Variable value change according to dimension in set analysis

pradosh_thakur — Fri, 05 Jan 2018 10:22:34 GMT

try this

sum({<Date={"<$(=date(max([SignUp date]),'YYYY-MM-DD'))"}>}orders)

sum({<Date={">=$(=date(max([SignUp date]),'YYYY-MM-DD'))"}>}orders)

Re: Variable value change according to dimension in set analysis

Anonymous — Fri, 05 Jan 2018 10:23:48 GMT

Each customer have their own signup date so it takes only min and max signup date from whole data model .

Re: Variable value change according to dimension in set analysis

pradosh_thakur — Fri, 05 Jan 2018 10:38:55 GMT

i have updated the app. check if this what you wanted ? if not than the expected output as per the data provided

Re: Variable value change according to dimension in set analysis

sunny_talwar — Fri, 05 Jan 2018 12:05:32 GMT

Do you happen to have a OrderID field in your database which is associated with a single date? If not, then you might have to use Aggr() and if to get this working... I don't think there is a way to do this on the front end with just set analysis

Re: Variable value change according to dimension in set analysis

pradosh_thakur — Fri, 05 Jan 2018 12:44:38 GMT

Hi Sunny

If i am understanding the requirement correctly, the QVW i have posted is showing the right amount before and after . Can you please make me understand what i am missing here?

when i am using max([Signup date]) its showing different result for diff customer. Isn't what the OP wanted ?

Re: Variable value change according to dimension in set analysis

sunny_talwar — Fri, 05 Jan 2018 12:53:07 GMT

I may be wrong, but when you use set analysis it is evaluated once per chart. For customer 3, the signup date is 2017-10-26, but the set analysis is still picking all order before 2017-10-30 for customer 3 also...

It happens that, in your sample there are no orders between 2017-10-26 and 2017-10-30, but if there were, then making selection in Customer 3 will change the numbers for Customer 3

Re: Variable value change according to dimension in set analysis

sunny_talwar — Fri, 05 Jan 2018 12:59:00 GMT

I have added one more record for 3...

LOAD * INLINE [

Cust, Date, SignUp date, orders

1, 2017-12-30, 2017-10-30, 0.49145

1, 2017-12-12, 2017-10-30, 0.98633

1, 2017-11-19, 2017-10-30, 0.32968

1, 2017-11-16, 2017-10-30, 0.24649

1, 2017-11-06, 2017-10-30, 0.19674

1, 2017-11-06, 2017-10-30, 0.19695

1, 2017-11-06, 2017-10-30, 0.19768

1, 2017-11-06, 2017-10-30, 0.19798

1, 2017-10-30, 2017-10-30, 0.33333

1, 2017-10-07, 2017-10-30, 0.5

2, 2017-03-25, 2017-10-20, 1

2, 2017-02-23, 2017-10-20, 0

2, 2017-12-30, 2017-10-20, 0.49145

2, 2017-12-12, 2017-10-20, 0.98633

2, 2017-11-19, 2017-10-20, 0.32968

3, 2017-11-16, 2017-10-26, 0.24649

3, 2017-11-06, 2017-10-26, 0.19674

3, 2017-11-06, 2017-10-26, 0.19695

3, 2017-11-06, 2017-10-26, 0.19768

3, 2017-11-06, 2017-10-26, 0.19798

3, 2017-10-30, 2017-10-26, 0.33333

3, 2017-10-27, 2017-10-26, 0.54

3, 2017-10-07, 2017-10-26, 0.5

3, 2017-03-25, 2017-10-26, 1

3, 2017-02-23, 2017-10-26, 0

];

Now check the expression with and without selection in 3

Re: Variable value change according to dimension in set analysis

pradosh_thakur — Fri, 05 Jan 2018 13:06:51 GMT

Thanks for the explanation !! As i said before learning new things everyday.

Re: Variable value change according to dimension in set analysis

Anonymous — Mon, 08 Jan 2018 02:27:48 GMT

Hi,

This is the only problem I am facing, It takes min and max date of all customers and evaluate. But every customer have their own signupdate

Please help me on this.

Thanks

Re: Variable value change according to dimension in set analysis

pradosh_thakur — Mon, 08 Jan 2018 07:56:40 GMT

Hi Paridhi

As Sunny explained Set analysis is evaluated once per the chart so you have to turn to other thing for example here scripting if you want row wise implementation like this .

Please change the script. it will definitely work for you.

regards

Pradosh

Re: Variable value change according to dimension in set analysis

Anonymous — Fri, 12 Jan 2018 03:22:01 GMT

Hi,

What I need to change in my script?

Thanks in advance

Re: Variable value change according to dimension in set analysis

sunny_talwar — Fri, 12 Jan 2018 08:58:14 GMT

I showed you what can be changed... don't like any of the ideas?