Qlik Community

Qlik Sense Integration, Extensions, & APIs

Discussion board where members can learn more about Integration, Extensions and API’s for Qlik Sense.

paridhimantri
Contributor

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
Valued Contributor III

Re: Variable value change according to dimension in set analysis

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

14 Replies

Re: Variable value change according to dimension in set analysis

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)

paridhimantri
Contributor

Re: Variable value change according to dimension in set analysis

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

pradosh_thakur
Valued Contributor III

Re: Variable value change according to dimension in set analysis

try this

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

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

paridhimantri
Contributor

Re: Variable value change according to dimension in set analysis

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

pradosh_thakur
Valued Contributor III

Re: Variable value change according to dimension in set analysis

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

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
Valued Contributor III

Re: Variable value change according to dimension in set analysis

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

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

Re: Variable value change according to dimension in set analysis

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

Community Browser