YTD missing values

[#1]

Dear all,
I've been struggling with this issue for days now, so let me try to explain this as good as I can.

First of I have an measure with the following YTD expression :

if(Match(Legal_Entity_AB,'ELECTRABEL','EEM','ENGIE SA','EGM','LPM','NGE'),

sum({$<YearAB={"=$(=Max(YearAB))"}, MonthAB={"<=$(=Max(MonthAB))"} >}AMOUNT_AB)/Weight,

sum(AMOUNT_AB)/Weight)

so as you can see from the if-condition , depending on which legal entity it is we get source data that is already YTD or just normal data/month.
But the behaviour that I have now while using this expression , is that if I add an extra dimension (all dimensions are dynamic) the grand total changes , because of the fact that for some months I dont have a particular value for that extra dimension.

See the first two attached images.

So in this case the row B - IT Costs doesnt appear in the 5 month, as it is not available in the 5th month , but as it is YTD I would like to see all possible rows that have appeared in the last 5 months , as shown in the 2nd picture, where I select the first 5 months and indeed the row B - IT Costs appear.

When I select a month where the IT Costs are propagated originally , like the 4th month shown below you see , he does calculate the correct YTD for that value ,and shows it.

This is really important as the grand total gets corrupted by this issue.

If anyone has any idea or possible direction I can look in , please feel free to advice or help me.

[#2]

Anyone?

[adamdavi3s]

Hi,

The simple solution would be adding a master calendar with a YTD flag then using this in the set.

I can see why your dashboard is failing, I am just trying to figure out if it is possible to work around or not.

[#4]

Hey Adam,

Thank you for the response , but could you elaborate on this approach? As on first sight I'd think it will give me the same result no?

Kind Regards

[adamdavi3s]

Hi,

No you're quite correct it does give you the same issue if you select a month, I hadn't thought it through fully.

The problem is that the match statement fails for month 4 because the record for that line doesn't exist.

I know i've had a similar issue before, I am just trying to pull it from the recesses of my mind

[adamdavi3s]

Ok I *think* I have it, it works in my stupid test scenario anyway which I have attached for you.

As with all things it probably wont work when you scale it up to your dashboard!

The match statement needs to be: if(Match(only({1}Legal_Entity_AB),'ELECTRABEL','EEM','ENGIE SA','EGM','LPM','NGE'),1,0)

[#7]

Hey Adam,

First of all I would like to thank you for helping me this profoundly.

What I see in your case is indeed a pointer in the right direction although , that in your case if you wouldnt do the only function in your match statement the row with the 0 value for that month is still visible. In my case that cost type doesnt exist for that month so he simply doesnt show it. If you look closely to the screenshot , you'll see that
B - IT Costs is indeed missing when selecting the 5th month.

And with your changes this issue sadly persists.

[adamdavi3s]

Hi,

I had noticed that of course and I exluded that value in my month4 as well, so I could recreate the issue.

There must be something else causing the issue, which is normally how it goes with trying to scale these up!

Can you provide a limited version of your dash?

[#9]

Hey Adam,

It is hard to send a working example of my dash , but I can give u the, lets say "front end" qvw , so u can see how I put all my parameters and set analysis for the expressions.

Find in attachment.

[adamdavi3s]

I think you must have some section access on there as I can't access it, sorry!