Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

set a value to calculate with

Hi,

I'm trying to a create table that calculates automatic. Therefor I've SET a value named Content='5'

Next I've created two inline table like this:

LOAD * INLINE [
content_objtype, name, total
0, Nieuws, 1000
1, Voorbeschouwing, 2000
2, Wedstrijdverslag, 2400
3, Column, 2345
4, Cast, 2000
5, Blaat, 5000
];

LOAD * INLINE [
content, F2
1, 2
2, 4
3, 6
4, 8
5, 10
];

Now I've created a textobject with the syntax::

=Sum (aantal)/F2

The outcome should be 1474,50 (because the sum total = 14745 and the F2 = 10m so 14745/10

The problem is that I do not get a value at all. What's wrong?

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Richard,

I guess you want "current" month not to be defined by the date you open application, but based on the last reload time. If my guess is correct, just change today() to the ReloadTime():
=num(month(ReloadTime()))

Another possibility is to to get last month from your data. I cannot give you the exact syntax without knowing more details, but the approach will be to create varialble vCurrentMonth in the script based on the last month in your data. It could be something like this, if your month filed in format 'YYYY-MM' is not a date format:


data:
LOAD
...
date(date#(DateField, 'YYYY-MM')) as DateField,
...
;
SQL SELECT ... FROM ...;
tmp:
LOAD
num(month(max(DateField))) as CurrentMonth
RESIDENT data;
LET vCurrentMonth = peek('CurrentMonth');
DROP TABLE tmp;


And, use vCurentMonth in your front-end calcualtions.

View solution in original post

6 Replies
Anonymous
Not applicable
Author

Richard,
if you want value of F2 for content=5 (which is 10), replace F2 in your formula with this:
only({$<content={'5'}>} F2)

Not applicable
Author

Hi Michael.

What I'm trying to do is set a month. The field Content is an example script.

So the script must automaticly get the right value from F2. I thought I would set the value to 5 (may) and with this the system calculated the right number. In June I would change the number to 6. My question is how to get this automatic?

January = 1
February = 2
...
...
December = 12

Any ideas?

Anonymous
Not applicable
Author

Well, this will be:
F2=num(month(today()))
So, the previous month is:
num(month(addmonths(today(),-1)))

Not applicable
Author

Thanks Michael, it almost works as I want it to. The thing is that with the =num(month(today())) formula you get the actual month. But If I open the model next month, the data wil change because of this value without reloading everything.

Once a month I update an Olap cube en after that, I reload my Qlikview file that imports the new cumulating data. When I open the QVW once a month after the reload, your formula works great, but when I want to open an older version, the month devider wil obviously change.

Is there a way to get Qlikview to know which month number it must calculate with looking to the imported data from the olap cube? The cube does have a date field (2011-01 / 2011-02 / 2011-03 ...) It must get the highest month, so in this example: March (3)

Anonymous
Not applicable
Author

Richard,

I guess you want "current" month not to be defined by the date you open application, but based on the last reload time. If my guess is correct, just change today() to the ReloadTime():
=num(month(ReloadTime()))

Another possibility is to to get last month from your data. I cannot give you the exact syntax without knowing more details, but the approach will be to create varialble vCurrentMonth in the script based on the last month in your data. It could be something like this, if your month filed in format 'YYYY-MM' is not a date format:


data:
LOAD
...
date(date#(DateField, 'YYYY-MM')) as DateField,
...
;
SQL SELECT ... FROM ...;
tmp:
LOAD
num(month(max(DateField))) as CurrentMonth
RESIDENT data;
LET vCurrentMonth = peek('CurrentMonth');
DROP TABLE tmp;


And, use vCurentMonth in your front-end calcualtions.

Not applicable
Author

Thx Michael! You helped me a lot!