Calculated Formula or Script ? [printscreens, tables attached]

[#1]

Hi Everybody,

Assume that I want to import data from the Excel file named data.xlsx :

Date	Hour	Value	Name
13/03/2011	00 :00 :00	123	Machine A
13/03/2011	00 :00 :00	698	Machine B
13/03/2011	01 :00 :00	178	Machine A
13/03/2011	01 :00 :00	720	Machine B
...
14/03/2011	00 :00 :00	625	Machine A
...

First, I would like to calculate the "sum" of "Value" everyday for each Name.

Idea number 1 : Import raw data and then use Calculated Formula

LOAD Date,
 Hour,
 Value,
 Name
FROM data.xlsx (ooxml, embedded labels, table is Sheet1);

Calculated formula will be something like this :

=sum(Value)

Idea number 2 : Import raw data and then use Calculated Formula

LOAD Date,
Hour,
Value,
sum(Value) as Cumul,
Name

FROM data.xlsx GROUP BY DATE&Name (ooxml, embedded labels, table is Sheet1);

Questions

Does it make sense ?
If yes, can you help me with the syntax
If not, can you explain me

[:)]

Last thing, what I can do with "expression" in =sum({expression} Value);
Some people use the sign "$" what is it ?

Best regards,

Jeremy

[#2]

Hi jeremy02 ,

Use the below code it works fine, i think..

LOAD Name,

Date,

sum(Value) as Value

FROM SourceFileName GROUP BY Name,Date;

About $:

if we create a variable like...

let i=10;

and later if we want to use the value of that variable, we simply write like this..

j=j+$(i);

[#3]

Hello Jeremy,

I suppose you are at the beginning of your affair with QV 😉 But to answer your questions:

Idea 1 or 2:

If there are no reasons like avoiding heavy load(-times) or performance problems I would load the raw data. This offers you the best flexibilty, one strength of QV. Unlke other BI-Tools you do not have to predefine any aggregation, cube or sth else during the loadprocess, with some exceptions like mentioned above. It is possible, like Arun said, but not necessary.

jeremy02 wrote:Last thing, what I can do with "expression" in =sum({expression} Value);

Within the brackets {} reside expressions belonging to the so called SET Analysis, a very powerful feature to precise what to do within your aggr-functions. See Help/Manuals for details.

jeremy02 wrote:Some people use the sign "$" what is it ?

The $()-Operator is used to indicate a variable. So $(vPrevWeek) is used in a expression and will return eg. 201114 when the (Text-)value of the variable vPrevWeek is "Week( Today()) -1". Again, don't hesitate to look for more details into the Help.

For testing it is often useful to write the expression within a textbox (with a leading "=" ).

HtH for the next steps

Roland

[#4]

Hi Aron,

it works well ! Thank you

Last question (before to verified the answer ) :

If I want to to make a difference between the first value of next day and the first value of current day instead of "sum" function

Example : (Assuming we have the same table)

I would like to get :

Date	Name	Value
13/03/2011	Machine A	502
14/03/2011	Machine B	-
...

502 because 625 - 123 for Machine A
- because x - 625
and so on

Is it more difficult ?

My aim is not to ask you to do my work but to understand how it works for expression

Thank you again