topic Re: FieldValue('NumAcquisto',FieldIndex('Label',Label) in App Development

FieldValue('NumAcquisto',FieldIndex('Label',Label)

ascioneanna — Fri, 07 Jul 2017 15:53:07 GMT

Hi Everyone,

I need an Help:

I need to create a measure to calculate N. Clienti and then %Clienti in another pivot table.

I want to create a pivot table with Client target 100% -->

All customers who made the first purchase in the period selected by the user in output box and among these customers those who have made at least 2 purchases, at least 3, ... Up to 10 (like in attached picture).

NumeroAcq:

Load * inline [NumAcquisto,Label

1,1° Acquisto

2,2° Acquisto

3,3° Acquisto

4,4° Acquisto

];

I create an inline table Only for the label but it is not linked to the tables previously loaded in the script.

Formulas:

sum(

if(

sum({<[Client ID]=p({<[Periodo Primo Ordine]={'$(vMeasure)'}>}),[Order State 2]={'Confermato'}>}[Numero Ordini])

>=FieldValue('NumAcquisto',FieldIndex('Label',Label))

,1,0))

It doesn't works because FieldValue('NumAcquisto',FieldIndex('Label',Label) has too many values.

Can Someone Help me??

I attach the qliksense app and the picture of what I want to replicate with qliksense.

Re: FieldValue('NumAcquisto',FieldIndex('Label',Label)

brunobertels — Sun, 16 Jul 2017 17:29:09 GMT

MAy be use the same approch than your cluster script with if statement

ClusterClienti:

Mapping

LOAD

[Client ID],

if([Numero Ordini per Cliente]>=4,

if(IntervalloMedioOrdiniCliente>=28,'Dormiente',

if(IntervalloMedioOrdiniCliente>=15 and IntervalloMedioOrdiniCliente<21,'Fedele',

if(IntervalloMedioOrdiniCliente>=21 and IntervalloMedioOrdiniCliente<28,'Poco Fedele',

if(IntervalloMedioOrdiniCliente<15,'Fedelissimo','')))),

if(maxData>(today()-28),'Non Monitorabili',

if([Numero Ordini per Cliente]=1 ,'Insoddisfatto 1',

if([Numero Ordini per Cliente]>=2 and [Numero Ordini per Cliente]<=3 ,'Insoddisfatto 2-3'

)))) as Cluster,

if([Numero Ordini per Cliente]>=10,'10° acquisto',

if([Numero Ordini per Cliente]=9,'9° acquisto',

if([Numero Ordini per Cliente]=8,'8° acquisto',

if([Numero Ordini per Cliente]=7,'7° acquisto',

if([Numero Ordini per Cliente]=6,'6° acquisto',

if([Numero Ordini per Cliente]=5,'5° acquisto',

if([Numero Ordini per Cliente]=4,'4° acquisto',

if([Numero Ordini per Cliente]=3,'3° acquisto',

if([Numero Ordini per Cliente]=2,'2° acquisto',

if([Numero Ordini per Cliente]=1,'1° acquisto')))))))))) as NumAcquisto

resident NumeroOrdiniRif;

then use dimension NumAcquisto in your graph

Or Like that :

in pivot table :

add this

Dimension :

=if([Numero Ordini per Cliente]>=10,'10° acquisto',

if([Numero Ordini per Cliente]=9,'9° acquisto',

if([Numero Ordini per Cliente]=8,'8° acquisto',

if([Numero Ordini per Cliente]=7,'7° acquisto',

if([Numero Ordini per Cliente]=6,'6° acquisto',

if([Numero Ordini per Cliente]=5,'5° acquisto',

if([Numero Ordini per Cliente]=4,'4° acquisto',

if([Numero Ordini per Cliente]=3,'3° acquisto',

if([Numero Ordini per Cliente]=2,'2° acquisto',

if([Numero Ordini per Cliente]=1,'1° acquisto'))))))))))

mesure :

Number :

sum({<[Order State 2]={'Confermato'}>}[Numero Ordini])

% :

sum({<[Order State 2]={'Confermato'}>}[Numero Ordini])

sum({<[Order State 2]={'Confermato'},[Numero Ordini per Cliente]={'1'}>} total([Numero Ordini]))

Bruno

Re: FieldValue('NumAcquisto',FieldIndex('Label',Label)

ascioneanna — Mon, 17 Jul 2017 08:56:27 GMT

thank you very much Bruno Your help has been awesome

Re: FieldValue('NumAcquisto',FieldIndex('Label',Label)

brunobertels — Mon, 17 Jul 2017 10:08:52 GMT

you're welcome

Re: FieldValue('NumAcquisto',FieldIndex('Label',Label)

ascioneanna — Mon, 17 Jul 2017 10:23:37 GMT

Can i ask you last thing about map with qlik sense? I haven't file.kml with postal code coordinates.

Map without latitude and longitude params

Re: FieldValue('NumAcquisto',FieldIndex('Label',Label)

brunobertels — Mon, 17 Jul 2017 10:34:09 GMT

See this post it may help you

https://community.qlik.com/docs/DOC-18766 https://community.qlik.com/docs/DOC-18766