Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a Dashboard with different Filters/Listboxes:
The Timefilters are related with my MasterCalendar PrimDef_DayGrid.
The listboxes are related:
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:
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:
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
I couldn´t find your qvw
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
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
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
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
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
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
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
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
Thanks Marcus for your help.