Error in formula

[#1]

Hi everybody!!

I´m triyng to build finance controlling data analisis to take decisions, so I study finance controlling.qwv standart. I want to see some comparing concepts of last three years, depending on the current selection year... for example:

One line with sales

Other one with ratio sales/profit

and year in columns. I think the way to do this could be introducing formulas in text objetct.

So, I select 2010 value on year field and I want to see sales and ratio sales/profit of 2010, 2009 and 2008.

The formula I´ve did on 2010 column is:

=

NUM(SUM(if(YEAR=GETFIELDSELECTIONS(YEAR) AND SUBCUENTA=7050000001,IMPORT,0)),'#.###,##') ------- DATA IS OK

The formula I´ve did on 2009 column is:

=NUM(SUM(if(YEAR=GETFIELDSELECTIONS(YEAR)-1 AND SUBCUENTA=7050000001,IMPORT,0)),'#.###,##') ----- DATA IS WRONG

Where is my error? Anyone can help me?

Thank you for dedicate me your time

Fiber

[Anonymous]

Fiber:
The problem with the 2nd formula is that you're calculating sum(IMPORT) for 2009, while the selected year is 2010. You have to use set analysis. I didn't show formatting for expressions to look a little simpler.
Selected year:
sum({$<SUBCUENTA={'7050000001'}, Year={"$(=only(Year))"}>} IMPORT)
Year before selected:
sum({$<SUBCUENTA={'7050000001'}, Year={"$(=only(Year)-1)"}>} IMPORT)

[#3]

I´m sorry Michael but I don´t understand your answer because I´m a beginer. All thinks I did with qvw it were easier than declaring variables. One think I don´t undertand in qlikview is the function structure, I mean, for example with SUM function:

NUM SUM ([DISTINCT][ALL][TOTAL] expr)

I have used always SUM(IMPORT) -most simple way-, but I don´t understand when you solve my problem with $ simbol, < simbol and > simbol. I suppose $ simbol is something like declaring VARIABLES in two arguments: first one is fixed in SUBCUENTA=7050000001 and the second one si YEAR depends on ONLY formula. Then appears " simbol in the begining and in the end and $ simbol I don´t understand. So, two questions:

Could you explain me how I must use that simbols to create more complecated functions?

What is the meaning of [DISTINCT], [ALL] and [TOTAL] in the formula?

I´ve been searching some manuals that explains some formulas but I didn´t found anything.

I hope you could understand my question. If you don´t I´ll try to explain it better.

Thank you too much

Fiber

[Anonymous · Accepted solution]

Hi Fiber,

1. The syntax with {$<...>} is from set analysis. It is not the easiest topic for a beginner. "Set analysis" was introduced by QlikTech recently, in QV 8.50. Although I've been using it for awhile, I didn't miss an opportunity to take set analysis training from QlikTech earlier this month. And the presenter asked if everyone in class had taken QlikView developers courses... It's not something I can explain in a forum post. I recommend you to take formal training. For the beginning, try to read and understand the OV help about set analysis.
I'll try to expalin this formula:
sum({$<SUBCUENTA={'7050000001'}, Year={"$(=only(Year))"}>} IMPORT)

It is basically sum(IMPORT), but with extras:
- the syntax {$<>} says it will be set analysis based on selections
- SUBCUENTA={'7050000001'} means to consider IMPORT values as if the value '7050000001' is selected in SUBCUENTA
- Year={"$(=only(Year)) means to consider IMPORT values as if you selected Year wich is equal to the selected Year (it actuialy is, so this part is optional)
You can find many questions and answers about set analysis on this forum. If you don't have a opportunity to take training, try to understdand these examples to see how it works. I think there are free online videos on QlikTech's site that may help.

2. The question about the qualifiers is easier.
DISTINCT means that aggrergation will use every unique value only once. It is often used in count() function (and almost never in sum()), so multiple occurence of the ame value will be counted only once.
ALL qualifier tells aggrergate function to ignore selections and dimensions.
TOTAL qualifier tells to ignore selections, but not dimensions.
Again, take a look in "help", there are examples that are very clear. These things do not require to take training 🙂

[#5]

Wow!! It was difficult but I´ve already got it!!! Now I undertand this structure, but I have to study more.

Take for sure we´ll see again in this forum. I like very much this program.

Thank you so much

Fiber