Expression problem

[#1]

Hi all,

I have got an Excel Pivot table  like the one below,

!

I have tried this in QV ,please the attached files

Dimension:=ValueList('C2D_Used','No_Of_Agents')  and Day

Expresion:

=pick(Match(ValueList('C2D_Used','No_Of_Agents'),

                  'C2D_Used','No_Of_Agents'),                

                 if(sum(if(CallTime > 0 , 1, 0)) > 0,'Y','N'),

                 Count(DISTINCT Consultant))

This does not give the needed result, it does not give me the 'N'. please assist.

PFA

[Anil_Babu_Samineni]

I didn't find Cash Investment at all on your Excel. Where do you pick that?

[sunny_talwar]

This does not give the needed result, it does not give me the 'N'.

What is the needed result? Can you share what you expect to see?

[#4]

Hi Sunny T ,

I actually want to convert the two expressions into dimensions, just as they are shown in the pivot.

Regards

[#5]

I actually want to convert the two expressions into dimensions, just as they are shown in the pivot.

[pathiqvd]

Hi,

You want like this.

[#7]

This exactly what I am looking for and the date should be per day.

[pathiqvd]

Hi,

you want like this,

[#9]

Exactly that

[pathiqvd]

Hi,

      K. Script level write like this:-

Test:

LOAD CC,

     Desk,

     Consultant,

     Team_Leader,

     Year,

     Month,

     Day,

     CallTime,

     interval(CallTime,'ss') as CallTime1

FROM

C:\Users\arul.settu\Desktop\Test_data.xlsx

(ooxml, embedded labels, table is Sheet1);

And Chart level

Dim:-

1)=ValueList('C2D_Used','No_Of_Agents')

2)=ID

Exp:-

=if(ValueList('C2D_Used','No_Of_Agents')='C2D_Used',if(count({<CallTime1={'0','00'}>}CallTime1)> 0,'Yes','No'),

                 if(ValueList('C2D_Used','No_Of_Agents')='No_Of_Agents',Count(DISTINCT Consultant)))