Set Analysis Returning Incorrect Values

[erictanzh]

Hi everyone,

I am very new to the coding, and I have a weird phenomenon affecting my set analysis results.

Essentially, I'm looking to only sum field values for only "Sep" dates. It performs its job to force all other months' values to zero but I'm seeing incorrect and even zero values being returned for selected September dates, resulting in incorrect summaries. Ideally, the rightmost column should correspond to sum(inwVolume).

Would a kind and enlightened please advise on my error, many thanks in advance.

[swuehl]

Could you add a inwMonth dimension to your table chart?

[erictanzh]

Hi Stefan,

I've created the inwMonth with a calendar script.

MstinwCalendar:

Load 

               TempDate AS inwDate, 

               week(TempDate) As inwWeek, 

               Year(TempDate) As inwYear, 

               Month(TempDate) As inwMonth, 

               YeartoDate(TempDate)*-1 as inwCurYTDFlag, 

               YeartoDate(TempDate,-1)*-1 as inwLastYTDFlag, 

               inyear(TempDate, Monthstart($(varMaxDate)),-1) as inwRC12, 

               date(monthstart(TempDate), 'MMM-YYYY') as inwMonthYear, 

               ApplyMap('QuartersMap', month(TempDate), Null()) as inwQuarter, 

               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as inwWeekYear

Resident TempinwCalendar 

Order By TempDate ASC; 

Drop Table TempinwCalendar;

[swuehl]

Yes, I've noticed that.

I meant, could you add the inwMonth field as dimension to your table chart and post the results, just like you did for the chart in your original post?

[erictanzh]

Hi Stefan,

Here it is, and it contains a trailing "."

I've played around with the data aggregation, and it seems that the dates operands are 'misbehaving'.

The "Count(inwSellerCode)" and "sum(inwVolume)" are returning correct values, as you can see the Sep.-2017 summation is still returning a subset figure.

Cheers in advance.

Eric

[swuehl]

Just to double check: You are not using multiple states in your app? And maybe having selections pending in default state?

[erictanzh]

Hi Stefan,

No, zero selections made.

This issue is truly getting my head bald.

Cheers,

Eric

[Lech_Miszkiewicz]

Interesting,

when you pull out inwMonth field into filterpane and apply selections does it filter numbers correctly in your table?

to me it looks like that you have calendar detached from your model and therefore it is not giving you right results, or the key field is not having matching values between fact and calendar.

Can you maybe apply floor() function on inwDate field both in calendar and in fact table just to make sure that the key is correct

regards

Lech

[zebhashmi]

try this

Sum({<inwMonth={'Sep'}>}inwVolume)