Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Variable value change according to dimension in set analysis

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-

   

CustDateSignUp dateorders
12017-12-302017-10-300.49145
12017-12-122017-10-300.98633
12017-11-192017-10-300.32968
12017-11-162017-10-300.24649
12017-11-062017-10-300.19674
12017-11-062017-10-300.19695
12017-11-062017-10-300.19768
12017-11-062017-10-300.19798
12017-10-302017-10-300.33333
12017-10-072017-10-300.5
12017-03-252017-10-301
12017-02-232017-10-300




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!

1 Solution

Accepted Solutions
pradosh_thakur
Master II
Master II

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

Learning never stops.

View solution in original post

14 Replies
sunny_talwar

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)

Anonymous
Not applicable
Author

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

pradosh_thakur
Master II
Master II

try this

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

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

Learning never stops.
Anonymous
Not applicable
Author

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

pradosh_thakur
Master II
Master II

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

Learning never stops.
sunny_talwar

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

pradosh_thakur
Master II
Master II

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 ?

Learning never stops.
sunny_talwar

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...

Capture.PNG

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

sunny_talwar

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