Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

arpitkharkia
Contributor

Concatenate dimesion with expression in pivot

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?

4 Replies

Re: Concatenate dimesion with expression in pivot

Then you should use an Aggr function. Try


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


as a calculated dimension.


HIC

arpitkharkia
Contributor

Re: Concatenate dimesion with expression in pivot

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?

Re: Concatenate dimesion with expression in pivot

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
Contributor

Re: Concatenate dimesion with expression in pivot

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.