Formula on scripting level - some syntax error?

[datanibbler]

Hi,

I would like to include a rather complex turnover formula that I currently have in the frontend of my document (in a diagram) on scripting level. That would make the structure simpler and in the long run, all those calculations should be "as deep as possible" in our multi-layered QlikView architecture so further documents can access the results like natural fields.

It seems, however, that there is some syntax error, QlikView does not accept this formula as a field though it does work.

Can somebody help me here? I will post my formula here:

= SUM({<Item_Type={"B"}>} Item_Price)

+ SUM({<Item_Type={"I"}>} ((PACKAGE_ITEMS.PACKED_QTY * ITEM_PACKTIMES.SALES_PACKTIME) + ITEM_PACKTIMES.SALES_PROCTIME) * $(v_Min_Price))

+ SUM({1<DepDay>}Fixkosten)

Thanks a lot!

Best regards,

DataNibbler

[Miguel_Angel_Baeyens]

Hi,

Basically, you cannot use set analysis in the script. Instead, you will have to use If()s to match the conditions you want to set. Note that you will need to do a GROUP BY and make sure that all fields in the formula are in the same table.

Hope that helps.

Miguel

[datanibbler]

Hi Miguel,

thanks for the answer!

There are, however, several issues with this:

- I don't want to aggregate (which I'd do with a GROUP BY combined with a SUM? Otherwise, by what would I
    need to group?)

- I cannot - or don't know how to - create the same thing using IFs: I want to make the display of fixed costs entirely independent of the date (DepDay) because the fixed costs come from an Excel table and they have no connection to the date, so originally, as soon as I selected one, they were ignored and that level was skipped. But I could drop that part of the formula and use that part in the diagram itself. It's actually only the variable_turnover_calculation that I want in the script.

Your last point is very good - all fields in the same table, that would mean I need a RESIDENT LOAD because I join that table with another one using a LEFT JOIN and in that second table, there's one field I use in my formula.

Best regards,

DataNibbler