Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cafcptg2011
Creator
Creator

pivot table expression doesn't work

Hi,

I need pivot table that shows the following data with these conditions:

     a) show only stores/clientes that SalesDate>StoreDate

     b) Value >1

     c) Number os sales >=2

my data:

X1.PNG

my pivot table is ok when full expanded...

x2.PNG

my expression in pivot table doesn't work ok when collapsed.. the total should be 3 not 5

x3.PNG

my current expression is:

if (count(Distinct if(SalesDate >= StoreDate and Value >1, SalesDate))>=2   , count(Distinct if(SalesDate >= StoreDate and Value >1, SalesDate)))

my qdw is here : http://community.qlik.com/servlet/JiveServlet/downloadBody/7951-102-1-10732/OnlySalesDateStoreDate_....

can some one help please...i realy need this...

thanks in advance,

CAFC

2 Replies
alexandros17
Partner - Champion III
Partner - Champion III

Take a look to Dimensionality() function, it may help

cafcptg2011
Creator
Creator
Author

yes, it help me your solution... thank you very much...

but, I noticed that the pivot table has strange behavior....

when is collapsed the dimension client still is visible, why? although, data is correct...

when is collapsed (the data is correct):

Capturar1.PNG

full expand (information is correct):

Capturar2.PNG

the correct would be:

Capturar3.PNG

my current expression is :

if (Dimensionality() = 1,  if ( Count(aggr(count(Distinct if(SalesDate >= StoreDate and Value >1, SalesDate)), Store, Client))>=2   , Count(aggr(count(Distinct if(SalesDate >= StoreDate and Value >1, SalesDate)),Store, Client)) ),

if (count(Distinct if(SalesDate >= StoreDate and Value >1, SalesDate))>=2   ,count(Distinct if(SalesDate >= StoreDate and Value >1, SalesDate)) ))

could someone please, refine my expression to get my pivot table correct...?? is it possivel? or is it normal like that?

my qvw is here: http://community.qlik.com/servlet/JiveServlet/downloadBody/7952-102-1-10763/OnlySalesDateStoreDate2....

thanks in advance,

CAFC