Maybe you can load your excel files with a wildcard, reading in all excel files in that directory, and then parsing the filename for ID an Month name, something along this lines:
subfield(FileName(),' ',1) as ID,
Text(date(addmonths(0,mid(subfield(FileName(),' ',2),1,2)),'MMMM')) as Month
(ooxml, embedded labels, table is Tabelle1);
Then create a pivot table with dimensions ID an Month and do a partial sum on dimension Month in presentation tab.
Hope this helps,
I already have a load statement which loads all Excel files from a specific folder
mid(FilebaseName(),3,5) as "PortimaID",right(FileBaseName(),2) as "maand",
(ooxml, embedded labels, table is [Selectie en Rapport]);
But the problem is... that the file for next month does not exist for the moment.
And thereby my total column shows NULL values.
because total = file 01 + file 02 + file03
but file 03 exists only within 14 days...
So for the moment i have to say:
total = file01+file02
and i have to change it next month.
That is what i don't want. I want to create the qvw file now and never change it again.
so where total = file01 + file02+ file03 + file04+...
So you are hardcoding your total as expression? And also the Months are separate expressions?
Try using a pivot with two dimensions, ID and Month (resp. PortimaID and maand) and one expression, I used the sum(Value).
Then use a partial sum (Total) in presentation tab for Month dimension. This should automatically reflect only the exisiting Months.
Or maybe I have misunderstood your problem?
TestMonthDyn.zip 131.8 K
I did a makeover of your files
add my table and syntax how i have it at the moment.
when you see at the expression total growth. there is my problem.
thanks for your help.
TestMonthDyn.zip 154.4 K