Using different filters and listboxes in a dashboard

[pacoli2013]

Hello,

I have a Dashboard with different Filters/Listboxes:

• · 6 Timefilters/Listboxes: Year, Quarter, Month, DayOfWeek, DayOfMonth and Week
• ·7  Listboxes:  Company, Source, Timefilter, DateFilter, Combobox with Filters, Currency and Language.

The Timefilters are related with my MasterCalendar PrimDef_DayGrid.

The listboxes are related:

• · Company with the field Company in my Facttable
• · Source with the Field Source in my Facttable,
• · Timefilter with the Inlinetable DateInterval
• · DateFilter with the Inlinetable TimeInterval
• · The Filters in the Comboboy with different fields in different related tables (not loading in this example)
• · Currency with the Currencytable loaded in the maintab
• · Language with the Languagetable loaded in the maintab, related with an excelfile 

In my fact-table I have two date-fields PostingDate and CreationDate. The 6 Timefilters should be used for both dates. That’s why I create the inline table TimeInterval and the Listbox DateFilter with Calendardate and PostingDate.

At the moment there is no relation between my mastercalendar and both dates in the facttable.

Using the expressions:

Year = if (DateFilterID = 1, year(GLEntryPostingDate), year(GLEntryCreatedDate))

Month = if (DateFilterID = 1, month(GLEntryPostingDate), month(GLEntryCreatedDate)) 

Day =  if (DateFilterID = 1, day(GLEntryPostingDate), day(GLEntryCreatedDate))

Week = if (DateFilterID = 1, week(GLEntryPostingDate), week(GLEntryCreatedDate))

In my Dashboard I have create some listboxes to check it, I also created a table of my FactTable with dimension GLEntryVoucher and several expressions.

The data in this table should react on the switch TillDate/ToDate, there for I build in some expressions like:

= if(Only(TOTAL {1<DateIntervalName = p(DateIntervalName)>} DateIntervalID) = 1, Only(GLEntryPostingDate), Only({$<Year=, Quarter=, Month=, MonthShortName=, Week=, Day=, DayName=, DayShortName=, Date = {"$(='<=' & Date(Max(Date)))"}>} GLEntryPostingDate))

This with help of Sunny Talwar (Thanks Sunny). When I activate the relation Date (of the MainCalendar) and CreatedDate (of the Facttable) the expression works oke. But it should also react on the listbox Datefilter and PostingDate, not only with CreatedDate.

With the listbox Datefilter  I can switch between both Calendardate and Postingdate, in a textbox I can see which date is choosen.

What are the requirements:

• Data in the Table Purchase Accruals should react on all possible filters/listboxes.
• When no selections made in the Listboxes, the filers should be set as follow:

1. Company à all companies,
2. Source à all sources,
3. Timefilter à TillDate,
4. Datefilter à Calendardate

• The Selection Status box should show which filters in the listboxes are set.
• The six Timefilters (top of the dashboard) should react on the Listbox DateFilter: when choosen CalendarDate, the timefilter Year should be the created-year, Month should be created-month, etc. For Postingdate: Postingdate-year, Postingdate-month etc.
• When elected Calendardate the  letters should be black in color, when Postingdate the colour should be blue.

I have created some extra listbox filters (TestYear, TestMonth, TestDays, TestWeek) for year, month, days and weeks  with the expression = if (DateFilterID = 1, month(GLEntryPostingDate), month(GLEntryCreatedDate)).

As long as I only make selections in the listboxes Company, Source, Timefilter, Datefilter (left on the dashboard), the selectionbox show s the right selections. At the moment I select a year in the listbox

At the moment I make selections in the test-listboxes my selection box goes “crazy” for instance testyear =2017 :

TimeIntervalID = 0,1

GLEntryPostingDate = 202 of 525

GLEntryCreatedDate = 190 of 508

It seems that the expression = if (DateFilterID = 1, month(GLEntryPostingDate), month(GLEntryCreatedDate)) doesn’t work properly.

In my facttable the data don’t react as the should be, expressions mentioned above should be update.

I have added a QVW with all filters, listboxes and selctionbox and facttable. When using the different possible settings, you will see what the problems as decribed, are.

My Questions:

1. What should I do to make the timefilters work properly
2. What should I do to make the expressions in the Listboxes work properly
3. How should the expressions in my facttable be updated, to act/work properly
4. How do I get the different colours for CalendarDate and PostingDate in the Timefilters
5. Should I create a variable for the listbox DateFilter instead of the InlineTable?
6. How can I prevent that my selectionbox goes crazy; TimeIntervalID = 0,1, GLEntryPostingDate = 202 of 525 and GLEntryCreatedDate = 190 of 508 should not be shown

In all: the filters / listboxes don’t work properly together. And the data in the facttable acts/works not properly with the filters when set.

I know many questions, but I think it is better to ask them all together, goes quicker I suppose.

Hope someone can solve them

Thanks in regards,

Court

[Clever_Anjos]

I couldn´t find your qvw

[marcus_sommer]

I think I would try to associate as much as possible within the datamodel probably by using multiple calendars and also by trying to merge them into a canonical calendar:

Why You sometimes should Load a Master Table several times

Canonical Date

and only if this isn't possible respectively it didn't cover all requirements I would add loosen tables of them to make certain selections or creating certain views which I would implement per set analysis within the expressions, maybe in this way:

sum({< SomeFactDate = p(SomeLoosenDimDate)>} AnyValue)

I hope it will give you some ideas ...

- Marcus

[pacoli2013]

Hello Marcus, I made a second Calendar for Postingdates, build some new filters and the work, so so far so good.

But the Users want to have a switch for today and till  today

The Problem is that the Expression = if(Only(TOTAL {1<DateIntervalName = p(DateIntervalName)>} DateIntervalID) = 1, Only(GLEntryPostingDate), Only({$<Year=, Quarter=, Month=, MonthShortName=, Week=, Day=, DayName=, DayShortName=, Date = {"$(='<=' & Date(Max(Date)))"}>} GLEntryPostingDate))

should be rebuild with Sum(AccrualGLEntryAccountingCurrencyAmount) in the expression, I tried

= if(Only(TOTAL {1<DateIntervalName = p(DateIntervalName)>} DateIntervalID) = 1, Only(Sum(AccrualGLEntryAccountingCurrencyAmount)), Only({$<Year=, Quarter=, Month=, MonthShortName=, Week=, Day=, DayName=, DayShortName=, Date = {"$(='<=' & Date(Max(Date)))"}>} Sum(AccrualGLEntryAccountingCurrencyAmount)))

but that didn't work

I also tried tho change my Dimension MainAccountID into a calculated Dimension:

= if(Only(TOTAL {1<DateIntervalName = p(DateIntervalName)>} DateIntervalID) = 1, Only(MainAccountID), Only({$<Year=, Quarter=, Month=, MonthShortName=, Week=, Day=, DayName=, DayShortName=, Date = {"$(='<=' & Date(Max(Date)))"}>} MainAccountID))

But that didn't either.

I learned that when you want to calculate a sum in a month, you use = Sum({$ < Year = {$(= max(Year))}> } Sum(AccrualGLEntryAccountingCurrencyAmount))

and when you want to calculate a sum from the first of a year until the month in the filter you use = Sum({$ < Year = {$(= max(Year))}, Month = {"<= $(=max(Month))"}, Quarter = > }Sum(AccrualGLEntryAccountingCurrencyAmount))

But when I use those last two expressions,  they work both the same, the results are only for the month equel the filtermonth.

Perhaps you have some ideas for me

Thanks in advance

[marcus_sommer]

It didn't work  because you are nesting aggregation-functions like only() and sum() without the use of an aggr(). This meant your expression need to be look like (simplified):

if(only(value) = 1,

     only(aggr(sum(value2), Dim1, Dim2)), only(value))

whereby I'm not sure if you really need this nesting - I think the sum() itself will be enough.

- Marcus

[pacoli2013]

Hello Marcus, thanks for your support. I need the nesting aggregration in every field of my table because when I don't used it, the fields in my table will be empty when I select 2017 as Filteryear and they have a postingdate in 2015 or 2016. Only when the data has a postingdate in 2017 the fields are filled.

So every field has next Expression, with the exception of the Sum-Fields:

= if(Only(TOTAL {1<DateIntervalName = p(DateIntervalName)>} DateIntervalID) = 1, Only(<FIELDNAME>), 

  Only({$<Year=, Quarter=, Month=, MonthShortName=, Week=, Day=, DayName=, DayShortName=, Date = {"$(='<=' & Date(Max(Date)))"}>} <FIELDNAME>))

For the Sumfields I tried :

= if(Only(TOTAL {1<DateIntervalName = p(DateIntervalName)>} DateIntervalID) = 1, Only(AGGR(SUM(<FIELDNAME>))), 

  Only({$<Year=, Quarter=, Month=, MonthShortName=, Week=, Day=, DayName=, DayShortName=, Date = {"$(='<=' & Date(Max(Date)))"}>} (SUM(FIELDNAME))))

But that didn't work. Perhaps you know what I have to change to make it work

Regards Court

[marcus_sommer]

Like above mentioned nested aggregations needs to be set with aggr-functions and this for each nesting with the appropriate dimensions. This meant the following couldn't work:

= if(Only(TOTAL {1<DateIntervalName = p(DateIntervalName)>} DateIntervalID) = 1, Only(AGGR(SUM(<FIELDNAME>), MissingDim)), 

  Only({$<Year=, Quarter=, Month=, MonthShortName=, Week=, Day=, DayName=, DayShortName=, Date = {"$(='<=' & Date(Max(Date)))"}>} (SUM(FIELDNAME))))

In general you could nest multiple aggr() but the complexity of the expression will be quite hard to handle and the performance will be slow to very poor.

I'm not sure if it could be solved in this way and would suggest you takes another look to my first reply on this question - I think it must be solved within the datamodel.

- Marcus

[pacoli2013]

Hello Marcus,

How can I see which Dimensions I have to add?

Do you mean with MissingDim the Dimension of the table, that would be in my case ReceiptLineOriginalPurchaseID.

But that still does not work:

= if(Only(TOTAL {1<DateIntervalName = p(DateIntervalName)>} DateIntervalID) = 1, Only(AGGR(SUM(ReceiptPurchaseOrderLineamount),ReceiptLineOriginalPurchaseID)), 

  Only({$<Year=, Quarter=, Month=, MonthShortName=, Week=, Day=, DayName=, DayShortName=, Date = {"$(='<=' & Date(Max(Date)))"}>} (SUM(ReceiptPurchaseOrderLineamount))))

Problem is the concept what is written to create this application. There has to be an Interval, a switch ToDate and TillDate and the condition that TillDate is everything till the date set by filters. Normally is it all in the year, month set by filters and there is no switch, that would be easier.

The DataModel hast three tables with postingdates, creationdates, shippingdates, requesteddates. So I created 

Extra Calendars and a CanonicalDate, extra Filters etc. but that was rejected by the users because it was not according to the concept. 

Court

[marcus_sommer]

Most often the needed dimensions of an aggr-function are those which are included within your table but more important is context in which this calculations must be done. This could leave some of the table-dimension but also using some other.

I think your task is quite complex and it's even more difficult to suggest an useful approach from the outside but I believe that you were nearer to a solution with your earlier attempt of using several and canonical calendars as with your now tried UI approach.

Here: The As-Of Table is another method how different dates could be linked to eachother - maybe it give you some new ideas how you could get to a solution.

- Marcus

[pacoli2013]

Thanks Marcus for your help.