Converting numbers to text, 1 = one ...

[sibrulotte]

Hi,

so I would like to show the number of periods as numbers spelled out for proper presentation. To top it off, in french!

I loaded inline this table as a data island: 

Chiffres:

Load * inline [
Chiffremois, Nombremois
1, un
2, deux
3, trois
4, quatre
5, cinq
6, six
7, sept
8, huit
9, neuf
10, dix

11, onze
12, douze
];

So you know, that's pretty french, but you can figure out what the english version is...

I figured I'd do a plain set analysis like this get back my "Nombremois"  

but it's not working out.

=Only({<Chiffremois=[Période financière]>} Nombremois)

BTW,  [Période financière] by itself gives me a 9 in a text box. No alternative states are involved.

Yet I still can't get it to return 'neuf' ...

Anybody want to contribute/lend a hand?

[stephencredmond]

Sorry, arriving late to this party.  I have noticed that your syntax is incorrect for the Set:

=Only({<Chiffremois={$(=[Période financière])}>} Nombremois)

Should give you the text that matches if the Période financière is a variable.  When I test this and set it to 9, the text I get is "neuf".

=Only({<Chiffremois={$(=[Période financière]-1)}>} Nombremois)

Will give "huit".

Of course, what happens when the variable is set to 1!  A simple if would work:

=Only({<Chiffremois={$(=If([Période financière]=1,12,[Période financière]-1))}>} Nombremois)

Regards,

Stephen

[sibrulotte]

Hi Période financière was not a variable. I tried storing the value in a vPériodefinancière variable to test what you were stating and I did not get a proper result for the above mentionned synthax with a -1.

I'll try and test it later when I actually have time to experiment further.

Also, this relates to a question I was asking with alternate states:

http://community.qlik.com/thread/109605

I am trying to retrieve a selection from the default alternate state and storing it to a variable to use in a set analysis of a "comparative" alternate state. If you find something there, it seems I'm never able to use variables in set analysis, and I'm getting frustrated .

last thing:

http://knowyourmeme.com/photos/653707-tuna-the-chiweenie

I laughed so hard when I read this, I thought you might also chukle at it

[stephencredmond · Accepted solution]

I guess that I am still not exactly sure what it is that you want to achieve.

If you look at the attached application, it shows some playing with disconnected data using Set Analysis.  I don't know if it is close to what you want.

Perhaps, if you step back from the specific problem and describe what you want to have happen then we can recommend a good approach?

Regards,

Stephen

[sibrulotte]

that actually works, I had just forgot that my initial load inline was done with a key to the calendar. and never went back to it. I took out the key, and the function works great.

Now, I added a test, if Période is lower than 7, no substarction, otherwise, substract 6.

See file.

6 works, but anything below returns null.

Any idea what is wrong there?

[stephencredmond]

Hi,

Because one of your expressions resulted in a return value of -1, this is invalid in a numeric set and needs to be quoted - even though that isn't the set that is used!

=if([Période financière]<7,Concat({<Chiffremois={$(=Max([Période financière]))}>} Nombremois, ',', Chiffremois),Concat({<Chiffremois={"$(=Max([Période financière]-6))"}>} Nombremois, ',', Chiffremois))

Stephen

[sibrulotte]

So I'm pretty much screwed, unless I set variables ahead according to the selection value I guess.

That's not going to happen since the boss said he prefers numbers now...

I love my job.