Is it possible to order the output of AGGR in a calculated dimension

[tracysmart]

I have a table where the user selects a start and end date and the pivot table will show columns of figures for the dates selected. I want to include a DayNo dimension, always starting at 1 regardless of what the start date is.

I can do this using an expression called DayNo in image below using

=ColumnNo(Total)

this works fine and it orders correctly as can be seen.

However, when I try to use a calculated dimension called DayNo (as I don't want it showing as an expression and repeating) I can get it to almost work but the ordering is wrong and no amount of playing with the sort order is solving this. I am using the following in my calculated dimension

=aggr((RowNo()), DiaryDate)

Has anyone achieved what I am trying to achieve and if so am I barking up the wrong tree completely?

Any help would be appreciated.

[sunny_talwar]

Can you share a sample application so that we can also play around with it

[tracysmart]

I have attached a cut down version of my app, it isn't reloadable I'm afraid but I hope it shows better what the problem is.

You will see the startdate and enddate calendar pickers and when you do select these, they filter the table but the calculated dimension will not apply the correct number.

[sunny_talwar · Accepted solution]

So I think the issue seems to be related to the load order of Diary Date. The first time you are loading in the field Diary Date, you need to make sure that you have a order by statement (Order By DiaryDate Asc).

Look at the screenshot and you will get the idea

11/06/2015 - > 1

12/06/2015 - > 2

20/06/2015 - > 3

21/06/2015 - > 4

and so on

Now your dimension (=Aggr(RowNo(), DiaryDate)) is creating row now based on this load order of DiaryDate.

Now the issue might be that you are unable to order the DiaryDate its entry, it which case you can create a table after your MasterCalendar load like this:

DiaryDate:

LOAD DiaryDate

          DiaryDate as DiaryDate1

Resident [Master Calendar]

Order By DiaryDate;

and use the dimension (=Aggr(RowNo(), DiaryDate1))

I am not 100% sure that this will resolve your issue, but I have good feeling that it should. Give it a try and let me know what you get.

Best,

Sunny

[#5]

Hi ,

sunindia‌ is right regarding this.The row number which is being populated is based on  the load order of the date field.

Is it required that you need to have DayNo before Diary Date.If not,you can simple have the Calculated dimension(Day No)=aggr((RowNo()), DiaryDate) after the Diary Date(i.e demote the current calculated dimension to the bottom of the three dimensions) and you will get the correct ordering.

Regards,

Jemimah

[tracysmart]

Jemimah, moving the position of the calculated dimension below DiaryDate looked like it had worked at first but it reordered the dates on the line above so these were now no longer in order.

It seems Sunny's fix was the one that sorted it and ensured that the order coming out of to AGGR function was correct.

Thank you for you help.

[tracysmart]

Sunny

Thank you for this – it was indeed down to the order in which the diary date was loaded in and I was able to deal with this in the script by doing a resident load from the QVD.

It is worth noting that I had to order in all tables and not just the calendar table so that I could put the calculated dimension either above the DiaryDate or below it and get consistent results.

I noticed that when no selections were made, and the chart was showing all data (for dates that had data, that is) - the DayNo was calculated from the first date in the data model and not the first date that had data and was showing in the chart. Therefore this only makes sense when the user selects a start date and end date.

[sunny_talwar]

Well I am glad it finally worked out for you . The changes you had to make, make perfect sense based on your requirement and data structure was. I wouldn't have known as I am not very familiar with the requirements as well as the data structure behind the scenes.

Best,

Sunny