topic On the fly SUM instructions depending dimension value + defaulting to set max value for a dimension after a reload or after opening document. in QlikView

On the fly SUM instructions depending dimension value + defaulting to set max value for a dimension after a reload or after opening document.

Mon, 13 Feb 2012 17:36:26 GMT

Dear all,

Please find enclosed qvw file which contain details for 2 kind of issues I have to deal with:

1. On the fly SUM conditions depending dimension values:

My Objective is to make the SUM with following condition: If COMMODITY like '%Soybeans%' then SUM(Soybeans + Crush - Soybeans) else If COMMODITY like '%Soymeal%' then SUM(Soymeal + Crush - Soymeal) else SUM per commodity, how can I deal with this???

2. Defaulting whern opneing a doc or after reload data:

After a reload or after opening the document, I want, by default, to make the 'Date' selection to the Max value.
Do I have to set a variable? How can I deal with this?

Thanks in advance for help!

Michaël.

On the fly SUM instructions depending dimension value + defaulting to set max value for a dimension after a reload or after opening document.

Anonymous — Mon, 13 Feb 2012 19:01:40 GMT

Question 1:

There are at least two options.

A. You want to merge Soybeans and Crush - Soybeans in the script. Use it if you never need them separately in your application.
LOAD
...
if(COMMODITY='Crush - Soybeans', 'Soybeans',if(COMMODITY='Crush - Soymeal', 'Soymael', COMMODITY)) as COMMODITY
...
FROM...

B. If you want to keep all values in the application, but use this rule only for that table, use calculated dimension
aggr(if(COMMODITY='Crush - Soybeans', 'Soybeans',if(COMMODITY='Crush - Soymeal', 'Soymeal', COMMODITY)) as COMMODITY),COMMODITY)

In both cases your expression will be the same
sum(TotalCash)

Question 2:

I'll answer later when have time, unless somebody else answers. There are many questions like this here, you can find out...

On the fly SUM instructions depending dimension value + defaulting to set max value for a dimension after a reload or after opening document.

Anonymous — Mon, 13 Feb 2012 19:40:40 GMT

Q2:

OK, it is a short answer anyway...

Create action on open:

Action type - selection,

Action - select in field

field - Date

search string - =max(all Date)

Re: On the fly SUM instructions depending dimension value + defaulting to set max value for a dimension after a reload or after opening document.

Tue, 14 Feb 2012 08:16:12 GMT

Thanks a lot for your inputs Michael, will try to day to deal with.

Thank you!

Michaël Remillier

Senior IT Business Analyst and Data Management

Grain Division

Noble Resources S.A.

A member of the Noble Group

207, route de Ferney

CH 1218 Le grand Saconnex - GENEVA

Direct: +41 21 331 16 56

Email: michaelremillier@thisisnoble.com

www.thisisnoble.com<http://www.thisisnoble.com/

Re: On the fly SUM instructions depending dimension value + defaulting to set max value for a dimension after a reload or after opening document.

Tue, 14 Feb 2012 09:35:59 GMT

Works! Thanks (I didn’t put the ‘all’ instruction in front of field name)

Bye!

Michaël Remillier

Senior IT Business Analyst and Data Management

Grain Division

Noble Resources S.A.

A member of the Noble Group

207, route de Ferney

CH 1218 Le grand Saconnex - GENEVA

Direct: +41 21 331 16 56

Email: michaelremillier@thisisnoble.com