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

[ascioneanna]

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.

[brunobertels · Accepted solution]

Hi

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

[ascioneanna]

thank you very much Bruno   Your help has been awesome

[brunobertels]

you're welcome

[ascioneanna]

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

[brunobertels]

Hi

See this post it may help you

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