topic Re: Excel file with expressions in QlikView

Excel file with expressions

Fri, 24 Apr 2015 12:49:45 GMT

Hi,

I have an excel file with a lot of expressions, like:

Média: avg(sales)

Soma: sum(sales)

and so on..

The problem is when i Load the file, it does not bring the results.

Can anyone help me?

Re: Excel file with expressions

Gabriel — Fri, 24 Apr 2015 16:58:05 GMT

Hi,

If you wanted to have the expression in excel as it is in qlikview, then do this

Data:
LOAD
Text(Sequencia) AS Sequencia,
Text(Descricao) AS Descricao,
Text(SubTotal) AS Subtital
FROM
[sample.xlsx](ooxml, embedded labels, table is Plan1);

But am guessing you want the expression to be calculated and resolved in qlikview?

Re: Excel file with expressions

jsakalis — Fri, 24 Apr 2015 18:36:46 GMT

Hi Diogo,

If I understand what you would like to do try the following:

1) Create a straight table and add the following calculated dimension:

=ValueList('Saídas','Débitos','Estornos','003 ESTORNO DE CRÉDITOS (DISCRIMINAR ABAIXO')

2) Create a expression:

Pick(RowNo(),

$(=Only({1<Sequencia={1}>} SubTotal)),

$(=Only({1<Sequencia={2}>} SubTotal)),

$(=Only({1<Sequencia={3}>} SubTotal)),

$(=Only({1<Sequencia={4}>} SubTotal)))

Sorry I am not able to test this without the QVW. Hope this helps.

Re: Excel file with expressions

NickHoff — Fri, 24 Apr 2015 18:48:43 GMT

Before loading your spreadsheet copy and then paste the cells as values and not formulas. Once you do this you'll have to create your expressions in the media you'd like to display them. Which also means you won't need to have to load everyone of them just the core that makes up the expressions.

Re: Excel file with expressions

Tue, 28 Apr 2015 14:55:12 GMT

Thanks for the answers guys, but the thing is:

When Qlikview load the row agv(sales) it cames like a text and it does not solve in qlikview.

I wold like to have a graphic where i could see the average or sum of sales.

This would be very easy if this was a variable. Insted is a colum from a table.

Re: Excel file with expressions

NickHoff — Tue, 28 Apr 2015 15:40:56 GMT

Based on the data you provided the problem isn't very clear. However, on your last statement "When Qlikview load the row agv(sales) it cames like a text and it does not solve in qlikview." It sounds like you are trying to load the data in a row, when Qlikview displays data in columns.

It sounds like you are attempting to use Qlikview incorrectly by not utilizing dimensions and metrics (your expressions). If you post your qvw it would be easier to help you or understand your problem.

Re: Excel file with expressions

awhitfield — Tue, 28 Apr 2015 15:48:01 GMT

Hi Diogo,

rather than trying to load them as expression, you need to load them first as variable, which can of course then be used as expressions. See the attached posting for further details:

https://community.qlik.com/thread/27883

Hope this helps,

Regards

Andy