Set Analysis aggregate with date range

[pirtekmismanage]

Hi community,

I have a set analysis which works fine until I have got one company group selected. If I select more company groups or clear the filter it doesn't show any figures. Just a bit explanation for the data set. Every group has got different start and end date it is like a membership expiration date. I would like to get the sales within their time of membership status. When selecting a month it nicely accumulate month by month and when it reaches the end date it will not show accumulation further only the sales up to the end date. As I said it works perfectly until I have got one company group selected. Any idea what is missing?

SUM(AGGR(SUM({<Event = {'Sales'}, GroupAccountType = {'National'}, New_Renewal={'Re-opened','New'}, DateKeyNum = {">=$(=StartDateNum)<=$(=EndDateNum)"}>} TotalExcVAT),Zone,GroupName,MonthYear))

Thanks in advance for the help.

[sunny_talwar]

Since the start and end date differ, you won't really be able to use set analysis... you might need to use if statement with aggr function. In case you need help with a specific expression, would you be able to share a sample to help you better

[ogautier62]

Hi,

what's the result ? a null value ?

if it's the case surely because sseveral aggr(   ) give same result,

try add nodistinct after aggr and before sum

regards

[vikramv]

you can try including the "company group" as one of the dimenions in the existing chart

If  you have multiple Group Names with the varying dates then definitely you have to include the "company group" as a  dimension in case you need to compare the data for themusing your current set expression.

====

You can cross check your data by creating a straight table by

Adding these expressions

SUM({<Event = {'Sales'}, GroupAccountType = {'National'}, New_Renewal={'Re-opened','New'}, DateKeyNum = {">=$(=StartDateNum)<=$(=EndDateNum)"}>} TotalExcVAT)

Maxstring(Date(Date#(StartDateNum))) ,

Maxstring(Date(Date#(EndDateNum)))

and check the result in a straight table to oserve what is causing your issue.

With Dimensions

Event , GroupAccountType , New_Renewal, ,Zone,GroupName, MonthYear

====

[pirtekmismanage]

Hi Olivier,

The values are zeros as it shows below. If I select one of the companies from the straight table chart then gets the real figures. And with selection.

[pirtekmismanage]

Hi Vikram,

I tested it and I still cannot see the issue. Max functions probably won't work as every company has got their separate start and end. With max I will pick the highest of all. I do not want that.

[pirtekmismanage]

Hi Sunny,

Sorry for the delayed reply I got busy. I am not sure how I can upload a sample dashboard. I will try to figure out and update the topic shortly. Thanks in advance for your help.

[pirtekmismanage]

Sunny,

I found help in one of your previous post "Upload Sample file" lol. You already helped!

Please see the sample file attached.

[sunny_talwar · Accepted solution]

Try this

Sum(Aggr(If(DateKeyNum >= StartDateNum and DateKeyNum <= EndDateNum,

Sum({<Event = {'Sales'}, GroupAccountType = {'National'}, New_Renewal={'Re-opened','New'}>} TotalExcVAT)),Zone, GroupName, MonthYear, DateKeyNum))

[pirtekmismanage]

Hi Sunny,

It marked. It works perfectly. I have tried before with IF but I used outside of the AGGR function so it hasn't worked for me. Can I have some explanation what is the difference withing the AGGR or outside of the aggregate? Is it basically part of the set analysis in this case or still not?

Really appreciate your help.

Thanks,