Identify upgrade/downgrade a record in a list

Hi,

Please go through the attached file.

here each userid has a plan and start /end date of each plan. User can upgrade/downgrade their plan. here i m looking at the solution where i can identify which is the latest plan for unique userId to consider them for the pricing.

Ex : User1 has A plan . after that he has upgraded to C plan. I want to identify that this is the latest plan to the current month.

Please help me with this.

Thanks,

Geet.

Capture_1_38767.PNG

Accepted answers

All comments

rob_marshall

Geet,

Try this out.

What was given (I used Row as the "row dimension" as none was given):

In the above, the User and Plans line items are list formatted line items where Plans has a Top Level Item defined (All Plans).

Create a module that is dimensionalized by Users (I call it People) and Plans with 3 line items: Start Date, End Date, and Plans Item.

The formulas are:

Start Date: Geet.Start Date[MAX: Geet.User, MAX: Geet.Plans]
End Date: Geet.End Date[MAX: Geet.User, MAX: Geet.Plans]
Plans Item: IF ISBLANK(Start Date) THEN BLANK ELSE IF Start Date = Start Date[SELECT: Plans.All Plans] THEN ITEM(Plans) ELSE BLANK

Set the Summary of the line items to be:

Create the Results module dimensionalized by People (users in the first picture) with Plan, Start Date, and End Date as line items.

Hope this helps,

Rob

zafkamar

Hi @geet.patel,

@rob_marshall's approach is a really good one. See below for another approach you could take:

This is a really great opportunity to use the power of saved views and sorting. What I would do to solve this problem is the following:

In the module where you have your data (let's call it Module 1), sort your dates from "highest to lowest". This will present the dates from latest date to earliest date.
Click View -> Save As... to save the sorted view of the module as a Saved View.
Create a new module (let's call it Module 2) dimensionalized by a UserID List and Line Items, where the line items are Plan, Subscription Start Date, and EndDate. Make sure you format those correctly.
On Module 2, click Data -> Import. Import data from the Saved View of Module 1, making sure to configure the import correctly. Most importantly, make sure to map the UserID text field from Module 1 into the UserID List in Module 2.
When you import from the Saved View, only the first records of each UserID will make it into Module 2. Since your saved view is sorted from latest to earliest, that means that only the latest records of each UserID will be brought into that module.

As always, there's loads more that you can do to make the process more efficient, but this is the basic premise. See attached images for the results!

Hope this helps,

Zaf

rob_marshall

@zafkamar

While that will work, it relies on an aciton which could lead to concurrency issues. Also, this will create a debug log as the action will not have green checks (clean load). Lastly, for the information to be updated, you will have to run this action, so the perferred way should be using formula's (less concurrency issues).

Thanks,

Rob

alexpavel

Hi,

I think it is needed a basic assumption: the "Valid From", "Valid To" intervals, for the same user, cannot have days in common, otherwise for the common days you can have "valid" in the same time 2 "Plan", which I do not think it is the case.

Based on the above assumption and considering that the current month can be considered as a "Current Date" defined in a general module as parameter you can have this solution:

- Current Date is Apr 12th 2018 in the module DP01

- Import all data in a numbered list in the module OP01

- formula "Current Plan" in OP01:

IF 'DP01. Paremters'.Current Date >= Valid From AND 'DP01. Paremters'.Current Date < Valid To THEN Plan ELSE BLANK

- Formula of "Current Plan" in OP02

'OP01. ALL Data'.Current Plan[FIRSTNONBLANK: 'OP01. ALL Data'.User Id]

Below a print-screen with the results presented in a dashboard:

Let me know if this is what you wanted

Ciao

Alex

Quick Links

Can you help?

Unanswered questions

how to count how many times an action has run?
I want to create a line item which will increment by 1 every time an action is run manually. This action is an import which doesn't involve any file upload to anaplan. The module where I want this line item has users list & SKU list dimension. No time. So basically, I want to track how many users have selected an SKU and…
Missing Column Headers in Anaplan Export When Using API
Hi Community, I’m encountering an issue where an export created in Anaplan is missing column headers when retrieved via the API. However, when I manually run the same export from the Actions tab, the downloaded file includes the headers. Has anyone experienced this behavior before? I’d appreciate any insights into the root…
Importing from Data Hub: Incorrect or Missing Mappings in Target Model
When importing data from a Data Hub to another model, I'm seeing values in the target model that don't exist in the source. For example, seller assignments are being tagged with subregions that aren't present in the source data - some assignments are missing expected tags, while others are mapped to seemingly random…