topic Re: pivot table expression doesn't work in QlikView

pivot table expression doesn't work

cafcptg2011 — Thu, 12 Feb 2015 18:47:43 GMT

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:

my pivot table is ok when full expanded...

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

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_.qvw

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

thanks in advance,

CAFC

Re: pivot table expression doesn't work

alexandros17 — Fri, 13 Feb 2015 15:45:39 GMT

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

Re: pivot table expression doesn't work

cafcptg2011 — Fri, 13 Feb 2015 17:15:01 GMT

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):

full expand (information is correct):

the correct would be:

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.qvw

thanks in advance,

CAFC