Calculation Not Calculating Correctly - Data Model Problem?

[pnn44794]

Hello:

I will close the other chat and give              Digvijay Singh credit for trying to help me (thanks Digvijay!!!).  So, in short, I'm not getting the correct result

As always, any and all help and responses are appreciated.  Thanks in advance.

[swuehl]

The reason why you get 53 is that your Sum aggregation is iterating over the records in table CPU-to-Memory-Mapping and hence can't exceed the sum of the record values --> 53.

Try mapping or joining the CPU2 values into the table where you actually want its records to be aggregated.

Don't know which table this should be, is it the E2E-DASHBOARD-DATA table?

How did you calculate your 1823 value?

[pnn44794]

Hello Stefan:

It is the E2E-DASHBOAED-DATA table I'm trying to use.  I get the 1,823 by an export to Excel and then a simple sum of the CPU2 column filtered by the service ordered column and ordered created year column (2018).

When you say and I apologize for not getting it, "Try mapping or joining into the table....", what or how do you mean doing that?

[swuehl]

If you export E2E-DASHBOARD_DATA table to excel, how can you sum up CPU2 values? These are not part of that table, right?

Try using a mapping table created by CPU-to-Memory-Mapping and then use ApplyMap() when loading your E2E-DASHBOARD-DATA table.

[swuehl]

I can get a sum of 1877 using

=Sum({<[Order Created Year] = {'2018'}, [Service Ordered] *= {'Virtual Server Infrastructure (VSI)', 'VSI - Exception Requests', 'VSI ICP2'}>} MEM*CPU2/MEM)

[the multiplication and division by MEM is needed to get a virtual table join of the two tables for the aggregation]

[pnn44794]

Ok.  I think this is the way to go...................but.  How do I code it?  I have not done a mapping table or an applymap (I get it that the two go together).  Please note that while my "status" may show I'm an "expert", I'm anything but that.

[swuehl · Accepted solution]

Have a look at

Don't join - use Applymap instead

and the document that is referenced at the end of the blog post.

Also the online help

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/MappingFunctio...

can give you a start.

[pnn44794]

Hello Stefan:

Thank you very much.  Applymap worked.  Thanks for the links.Again, thank you very much for your help.  I appreciate it.  Sometimes, that's all I need.  Just to be pointed in the right direction.  Thanks and enjoy the rest of your weekend.