Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

paolojolly
New Contributor III

Filtering pivot with date variables

Hi I'm new in Qlikview and I need help to create a pivot table from the follow excel table

C_CLIENTEC_LINEA_FATC_LISTINO_RIGADATA_FATTURADATA_RIF_DOCGroupmeseP_SC_CAN1_CNTP_SC_CAN2_CNTP_SC_CAN3_CNTP_SC_COMP_CNTP_SC_DERIVA_COMPLETA_CNT
C000000900500008515/01/200907/01/20095880,000,19000,10,450001666
C000000900500008515/01/200912/01/20095880,000,19000,10,450001666
C000000900500008515/01/200913/01/20095880,000,19000,10,450001666
C000000900500008515/01/200914/01/20095880,000,19000,10,450001666
C000000900500008531/01/200915/01/20095880,000,19000,10,450001666
C000000900500008531/01/200916/01/20095890,000,19000,150,480557129
C000000900300008531/01/200920/01/20095860,000,190000,38889074
C000000900300008531/01/200928/01/20095860,000,190000,38889074
C000000900300008515/02/200903/02/20095860,000,190000,38889074
C000000900300008515/02/200911/02/20095860,000,190000,38889074
C000000900300008528/02/200917/02/20095860,000,190000,38889074
C000000900300008515/03/200903/03/20095860,000,190000,38889074
C000000900300008915/03/200903/03/20095870,000,190000,38889074
C000000900300008915/03/200911/03/20095870,000,190000,38889074
C000000900300008931/03/200917/03/20095870,000,190000,38889074

in qlikview document I added two slider object for filterig by DATA_RIF_DOC

In the pivot i need to have C_CLIENTE as dimension and as expression for the max Group field for the selection period the values of fields

P_SC_CAN1_CNT

P_SC_CAN2_CNT

P_SC_DERIVA_COMPLETA_CNT

MIN(DATA_RIF_DOC)

MAX(DATA_RIF_DOC)

I attach a sample document with data

I have tried using FirstSortedValue.

is the correct way?

Can someone help me?

thanks

4 Replies
Not applicable

Re: Filtering pivot with date variables

Ho Paolo,

didn't catch exactly what you want, so check my guesses.

Hope this points into right direction.

Roland

ashwanin
Valued Contributor

Re: Filtering pivot with date variables

Hi ,

Firstly you need to upload the excel file ( which contains data only) in the QV and the create Pivot table there.

Happy Qliking

paolojolly
New Contributor III

Re: Filtering pivot with date variables

Maybe I have not explained properly my problem. For example, i set date variable in the slider from 10/06/2009 to 13/01/2009

C_CLIENTEC_LINEA_FATC_LISTINO_RIGAmeseDATA_FATTURADATA_RIF_DOCGroupP_SC_CAN1_CNTP_SC_CAN2_CNTP_SC_CAN3_CNTP_SC_COMP_CNTP_SC_DERIVA_COMPLETA_CNT
C00000090030000890,0015/06/200903/06/20095870,190000,38889074
C00000090030000890,0015/06/200910/06/20095870,190000,38889074
C00000090030000890,0030/06/200916/06/20095870,190000,38889074
C00000090030000890,0030/06/200924/06/20095870,190000,38889074
C00000090030000891,0015/07/200930/06/20095870,190000,38889074
C00000090030000890,0015/07/200908/07/20095870,190000,38889074
C00000090030000890,0015/07/200914/07/20095870,190000,38889074
C00000090050000850,0015/01/200907/01/20095880,19000,10,450001666
C00000090050000850,0015/01/200912/01/20095880,19000,10,450001666
C00000090050000850,0015/01/200913/01/20095880,19000,10,450001666
C00000090050000850,0015/01/200914/01/20095880,19000,10,450001666
C00000090050000850,0031/01/200915/01/20095880,19000,10,450001666
C00000090050000850,0031/01/200916/01/20095890,19000,150,480557129

and in the pivot i need to get only 1 record like

C_CLIENTEGroupP_SC_CAN1_CNTP_SC_CAN2_CNTP_SC_CAN3_CNTP_SC_COMP_CNTP_SC_DERIVA_COMPLETA_CNTmin
DATA_RIF_DOC
max
DATA_RIF_DOC
C00000095880,19000,10,45000166610/06/2009

13/01/2009

in which for the max  Group values in the selection period(588) are shows the corresponding values ​​of

P_SC_CAN1_CNT
P_SC_CAN2_CNT
P_SC_CAN3_CNT
P_SC_COMP_CNT

P_SC_DERIVA_COMPLETA_CNT

and the min e max values of field DATA_RIF_DOC

thanks for your help

Not applicable

Re: Re: Filtering pivot with date variables

Pls check pivottable RK in attached example.

BTW: Is

Min(DATA_RIF_DOC)  = 10/06/2009 ?

max(DATA_RIF_DOC)  = 13/01/2009 ?

correct ?

Do you mean 13/01/2010

Community Browser