Concatenate dimesion with expression in pivot

[arpitkharkia]

Hi All,

I am trying to concatenate a dimension and expression and create a new calculated dimension. Consider the following example

i have MonthYear dimension and Sales dimension. I am trynig:

=MonthYear&'-'&sum(sales)

which would give me,

Jan-18-500

Feb-18-200

and so on.

Can someone help me with this?

[hic]

Then you should use an Aggr function. Try

=Aggr(MonthYear&'-'&sum(sales),MonthYear)

as a calculated dimension.

HIC

[arpitkharkia]

Thanks HIC. Its working just fine but i ran into a situation. If you look at the following snapshot.

The aggr() is working just fine but what i need is sum(setup) where Setup and collection month is same. So basically the setup amount displayed in the pivot has to be in the dimension.

OR

is it possible to show the setup amounts all in one column right next to setup month as a dimension?

[hic]

I'm not sure I understand what you try to achieve. But let's start with what you want to show, and leave where you want to show it until later.

You obviously have two different months in your data model: Collection_Month and Setup_Month. Further, a record can be associated with one Collection_Month and another Setup_Month (this is visible in the chart). And now you do not want to include records that have different months in these two fields?

If so, you should perhaps try

=Aggr(If(Setup_Month =Collection_Month,Sum(sales)),Setup_Month ,Collection_Month)

HIC

[arpitkharkia]

Exactly my data model has 2 dates. The snapshot above is basically what i want, and its kind of perfect, but for the ease of readability of data i want all the setup amount in one column. It should split by Setup and collection date.

Condition- I only need those records where my collection and setup dates are same.