topic Re: YTD missing values in QlikView

YTD missing values

Wed, 27 Jul 2016 14:57:26 GMT

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.

Re: YTD missing values

Tue, 02 Aug 2016 09:22:58 GMT

Anyone?

Re: YTD missing values

adamdavi3s — Tue, 02 Aug 2016 14:52:17 GMT

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.

Re: YTD missing values

Wed, 03 Aug 2016 07:12:32 GMT

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

Re: YTD missing values

adamdavi3s — Wed, 03 Aug 2016 07:49:32 GMT

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

Re: YTD missing values

adamdavi3s — Wed, 03 Aug 2016 07:54:02 GMT

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)

Re: YTD missing values

Wed, 03 Aug 2016 08:16:42 GMT

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.

Re: YTD missing values

adamdavi3s — Wed, 03 Aug 2016 11:42:01 GMT

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?

Re: YTD missing values

Wed, 03 Aug 2016 12:17:40 GMT

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.

Re: YTD missing values

adamdavi3s — Wed, 03 Aug 2016 12:50:43 GMT

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

Re: YTD missing values

Wed, 03 Aug 2016 13:22:14 GMT

I'm sorry I completely forgot, normally it should be accessible now.

Re: YTD missing values

adamdavi3s — Thu, 04 Aug 2016 06:27:20 GMT

Same issue I am afraid.

Did you:

1- Delete the section access script

2- uncheck the section access options in document properties

3- reload the dashboard ?

Re: YTD missing values

Thu, 04 Aug 2016 07:55:13 GMT

Hey,

I was about to re-upload but thinking about it overnight , I'm sure you won't be able to have any information out of this qvw.

If it suits you , I'd propose to hold a small teamview session? This way I can show you the report as is in fully functional form and you get the best feeling of the issue.

Let me know

Kind Regards

Re: YTD missing values

adamdavi3s — Mon, 08 Aug 2016 13:36:34 GMT

Hi,

Sorry I was off for a few days.

Unfortunatly I wouldn't be able to teamview at work as much as I think that would save some time!

Can you copy the relevant objects into a new dashboard with a 'cleansed' small subset of your data?