I have attached some data.
Sheet 1 shows given sample data.
I want for every date to insert 4 new rows in the table, which are:
K=(C+F+G+H+I) / A, which is the same with K= J / A
M=(B+D+E) / A, which is the same with M = L / A
Sheet 2 shows how I would like the resulting table to look like. (I made manual calculation only for first 2 months)
Thank you for your time.
TestData.xlsx 13.8 K
maybe one solution to use your existing formulas might be something like:
table1: LOAD Date, F1, Actual FROM [https://community.qlik.com/servlet/JiveServlet/download/1286541-282852/TestData.xlsx] (ooxml, embedded labels, table is Sheet1); tabTemp: Generic LOAD * Resident table1; tabTemp2: LOAD 1 AutoGenerate 0; FOR i = NoOfTables()-1 to 0 STEP -1 LET vTable=TableName($(i)); IF WildMatch('$(vTable)', 'tabTemp.*') THEN JOIN (tabTemp2) LOAD * RESIDENT [$(vTable)]; DROP TABLE [$(vTable)]; ENDIF NEXT i CrossTable (F1, Actual) LOAD Date, C+F+G+H+I as J, (C+F+G+H+I) / A as K, B+D+E as L, (B+D+E) / A as M Resident tabTemp2; DROP Table tabTemp2;
hope this helps
QlikCommunity_Thread_263822.qvw 157.5 K
That is absolutely beautiful Marco. You are a real legend
My data has another column (budget), but I was able to squeeze that in by applying your solution twice and then joining the two tables.
I will now apply it to my real data, which has more information and calculations, but I think it will work.