How to show only one dimension value in a chart

[nimeshpa]

I have a dimension that has changed over  a year that is causing the issue in my current year / Prior year compare chart. For example, ID 975 is changed to Philadelphia for Fiscal Year 2015, whereas, it was Arizona for Fiscal year 2014. Now when I create a chart with current year / prior year compare,  with dimensions as ID & Name, it will by default show me Philadelphia and Arizona both, with current year metric as '0' for Arizona and last year metrics as '0' for Philadelphia.  Is there a way to merge them into one row with latest name based on selection, i.e. (2015 = Philadelphia) as Arizona doesn't exist in 2015? As of now you will see something like this as soon as users select 2015.

A QVW is attached with sample data. This is just an example, and there are a lot of movements across years with many names and IDs. Thanks.

[HirisH_V7]

Hi Nimesh,

There you are acquiring data for previous year in one of the expression such that the ID values are Mapped as Two .If we restrict by aggr or any function means your result will get effected i think.

Regards,

hirish

[#3]

Hi Nimesh,

You can use something like this calculated dimension if it is a specific case :

if(Name='Arizona','Philadelphia',Name)

Thanks

Khushboo

[jonathandienst]

Remove Name as a dimension. If you want to show the names, create an expression:

     =Concat(Name, ', ')

And add that at the same position as the current dimension.

[PrashantSangle]

Hi,

As suggested by jontydkpi‌ You can remove Name as dimension and add as expression with

concat(Name,',')

or

In script create derived field like

if(Id='975' and Fiscal_Year='2015','Philadelphia', Name) as modified_Name

Regards

[ElizaF]

Hello,

First, I recommend to remove the synthetic key that consists of fields ID and FiscalYear. It can affect your application performance.

You can try the applymap function to create a new dimension to cover the use case when IDs name could be changed.

Have a look on attached file.

Hope this helps.

[Peter_Cammaert]

I think the best thing you can do from a data model-perspective is to (maybe create a real dimension table if it doesn't exist yet and) add an extra Name to all ID's that contains the most recently designated Name of every ID. Like for example:

Areas:

ID, Year, Name, LastName

975, 2014, Arizona, Philadelphia

975, 2015, Philadelphia, Philadelphia

;

A composite key will connect to every individual record, and the usage of LastName instead of Name will bring all amounts together.

[vincent_ardiet]

Hi,

Why aren't you trying to obtain the information in the script?

For example if your main table is FactTable.

lastID:

LOAD ID,Max(FiscalYear) as FiscalYear Resident FactTable Group by ID;

Inner Join (lastID)

LOAD Distinct ID,FiscalYear,Name Resident FactTable ;

Inner Join (FactTable)

LOAD ID,Name as LastName Resident lastID;

Drop Table lastID ;

Then you can use LastName in your dashboard.

[Mark_Little]

Hi,

You could do in script.

If (ID =975, 'Philadelphia', Name)  AS Name

Then you won't need to change anything front end.

Mark