How can I reference the values from Dimensions in set analysis?

[#1]

Hi all,

I was hoping to tap into your knowledge for help with this issue I'm having.

What I'm trying to do is with set analysis measure the decline in user activity, using a log of transactions.

The logic is that any user that completed a transaction last month and hasn't done any this month should be counted against the month they're missing from.

To help, I've created a field called "LapseTransactionMonth". This field is the transaction created month forward-dated by 1 month.

So, the basic code would be:

//All users with transactions in the previous month

Count({<LapseTransactionMonth = {"=CurrentMonth"}>} Distinct UserID) -

//Subtract subset of users with transactions this month

Count({<LapseTransactionMonth = {"=CurrentMonth"}, TransactionMonth = {"=(CurrentMonth)"} >} Distinct UserID)

Note, CurrentMonth is meant to signify the month dimension on the visualisation; so if there are 7 months worth of data, this formula should be applied to each of them.

Sofar, I've not been able to crack this.

Thank you for your help.

Sven

[Gysbert_Wassenaar]

Perhaps this document helps: set_analysis_intra-record.qvw

[#3]

Thanks Gysbert, but I'm using Qlik Sense and can't open the attachment to see the formula.

[Gysbert_Wassenaar]

Ok, I'll put a Qlik Sense version on my todo list. For now you'll have to make do with the attached image.

[vinieme12]

Try the below

=

Count({<LapseTransactionMonth = p(CurrentMonth)} Distinct UserID)

-

Count({<LapseTransactionMonth = p(CurrentMonth), TransactionMonth = {"=(CurrentMonth)"} >} Distinct UserID)

[#6]

Thanks Vineeth. I tried something similar, but the problem is that I don't know how to reference "CurrentMonth".

The chart I'm working on has TransactionMonth as the Dimension. The formula I'd like to build should use the dimension value itself.

So for July 2016, I'm trying to get a count of people who did a transaction in June 2016 but not in July. And looking at June 2016 I'd like to see how many did nothing in June but have done something in May, etc.

Basically, I'm trying to find a way of having the comparison not be against a single value for the whole visualisation, but instead consider each value in the dimension.

[#7]

Dankjewel, Gysbert!

Also, I've looked at your solution and I think the problem I've got is that I don't have a scenario where I'd be comparing values from the same row.

Staying with your order scenario, let's say there were 2 customers with an order each last month (i.e. 2 order rows) and only one customer placed another order this month (another order-row).

I'd like the customer that didn't do anything this month to get counted.

This logic should then ideally be applied from the perspective of the other months and then get shown in the chart together - so that each value of the month-dimension shows how many customers stopped doing business in that month.

[vinieme12]

Hi ,

Please see attached QVW.

I've flagged transactions for each customer for each month, hope it helps.

[vinieme12]

Hi Sven,

Attaching a sample aggregated by yearmonth.

Cheers

V

[vinieme12]

Hi sven.skottke

Have you found a solution?can you share?

Thanks

Vineeth