date from root data vrs. Master Calender in set analysis

[#1]

Folks,

I am having troubles with the following issue.

I am having a date in my root data while loading:

-  Date(DataDate) AS DataDateDaily,

- date(monthstart(DataDate),'YYYY MMM') AS DataDateMonthly

Additionally I have created a Master Calender

- Date(TempDate, 'DD.MM.YYYY') As MasterDate,

- date(monthstart(TempDate),'YYYY MMM') AS MasterYearMonth

The link between the tables works fine and is used for many selections. I also allow the user to choose from the Master Calender (Master...) Dates.

Now I set up two set analysis:

Doing a set analysis based on the Master Calender Date:

=sum({<MasterYearMonth={"$(=Date(Today(), 'YYYY MMM'))"}>} [Outbound])

Doing a set analysis based on the Root Data Date:

=sum({<DataDateMonthly={"$(=Date(Today(), 'YYYY MMM'))"}>} [Outbound])

The Master Calender Set Analysis returns "0" when I am selecting another month (not the current one) in my data picker from my Master Calender. The Set Analysis based on the "DataDateMonthly" in always returning the correct sum of Outbound, no matter what I am selecting in the Master Calender.

I want to achive to put the Set Analysis based on the Master Calender, but also always return the correct SUM of Outbound, no matter what I have selected in the Master Calender.

How is this possible?

Thanks,

Mathias

[jonathandienst]

Hi

You say the link between the data dates and master calendar is working, but from what you have posted there is no association between the master calendar and the data date fields. Without an association between the tables, making selections in one (the master calendar) will have no effect on the other.

I am not sure what you are trying to achieve in your set analysis expressions. They will ignore the selections made by the user as they are forcing the data month and master calendar month to the current month (month of Today()).

To analyse this further, I would need to look at the chart/table and see the dimensions used, and look at the data model as well. I suggest that you post your app or a sample.

Regards

Jonathan

[#3]

Hi Jonathan,

the link is existing. While executing the script it does some more as you can see of course. Please find attached a JPG from the table overview.

I want to achive, do enter set analysis based on the MasterCalender, but always (no matter if I have chosen some date field in a listbox or not) give the SUM of the current Month. Meaning:

--> =sum({<MasterYearMonth={"$(=Date(Today(), 'YYYY MMM'))"}>} [Outbound])

Shall return always the SUM of Outbound.

For the moment I am just able to achieve this with using the DataDateMonthly in the set analysis; but this will not work for further set analysis, as I  would like to use also WeekDays, etc. for further analysis. And this dates I just have in the MasterCalender.

in Addition, here is how i setup the Master Calender:

GetCalenderData:
Load
               min(DataDateDaily) as MCminDate,
               max(DataDateDaily) as MCmaxDate
Resident IMPORT_DATA;
Let vMCMinDate = Num(Floor(Peek('MCminDate', 0, 'GetCalenderData')));
Let vMCMaxDate = Num(Floor(Peek('MCmaxDate', 0, 'GetCalenderData')));
Trace MIN $(vMCMinDate);
Trace MAX $(vMCMaxDate);
DROP Table GetCalenderData;
TempCalendar:
LOAD
               $(vMCMinDate) + Iterno()-1 As Num,
               Date($(vMCMinDate) + IterNo() - 1) as TempDate
               AutoGenerate 1
               While $(vMCMinDate) + IterNo() -1 <= $(vMCMaxDate);
MasterCalendar:
Load
               TempDate AS DataDateDaily,
               num(TempDate) AS MasterDateNum,
               Date(TempDate, 'DD.MM.YYYY') As MasterDate,
               date(monthstart(TempDate),'YYYY MMM') AS MasterYearMonth,
               week(TempDate) As MasterWeek,
               Year(TempDate) As MasterYear,
               Month(TempDate) As MasterMonth,
               Day(TempDate) As MasterDay,
               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
               WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

[jonathandienst]

Mathias

Your script looks OK (a little inefficient but should work as expected), and assuming that the metric fields like Outbound are in the IMPORT_DATA table, the expressions should work. Although I am still confused as to what you are trying to get with the expressions. As the date is fixed on Today(), what are the user selections supposed to do?

Without a sample, it is not possible to investigate deeper (looking at chart/table dimensions for example) so I all I can do is guess. Perhaps your set expression needs to explicitly exclude selections on some of the other date fields using the syntax

=sum({<DataDateMonthly=, MasterYear=, ... etc ... MasterYearMonth={"$(=Date(Today(), 'YYYY MMM'))"}>} [Outbound])

Regards

Jonathan

[#5]

Gents, thanks for all your hints. Finally the answer of Jonathan and my investigations after hours crossed. I guess I found my mistake finally. I have to exclude all date selections from the Master Calender.

Please find attached my demo application I wrote. The user selection, dear Jonathan, shall enable the user to e. g. set the selection to a specific dealer.

I still don't understand why I have to exlcude all rows (MasterYear, MasterMonth, etc.) from the MasterCalender. But finally it is working as I expect it by doing this.

Maybe this can somebody answer to me,

Thanks,

Mathias