Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Expression problem

Hi all,

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

Sample Data.PNG!

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

10 Replies

Re: Expression problem

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

Re: Expression problem

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?

Not applicable

Re: Expression problem

Hi Sunny T ,

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

Regards

Not applicable

Re: Expression problem

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

pathiqvd
Contributor III

Re: Expression problem

Hi,

You want like this.

Test1.JPG

Not applicable

Re: Expression problem

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

pathiqvd
Contributor III

Re: Expression problem

Hi,

you want like this,

Test2.JPG

Not applicable

Re: Expression problem

Exactly that

pathiqvd
Contributor III

Re: Expression problem

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