Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
AlexWest
Contributor III
Contributor III

If And. Help to unit them

Hi guys!

I'm trying to insert code into KPI Panel:

if([New-old_client] = 'Old' and [New-old_client] = 'New', '1')

[New-old_client] is being chosen usin Filter Panel

But I see just MINUS symbol (pic attached)

The original code is

if(SUM_CHANNEL1 = '1',
if([New-old_client] = '2', Count(Client_ID)/2,
if([New-old_client] = '1', Count(Client_ID)/3,
if([New-old_client] = '2' and [New-old_client] = '1', Count(Client_ID)/5))))

So as u can see, I'm trying to check 3 conditions, and 1st and 2nd checks are work, but this 3rd one doesn't, so that and the top command as well.

Please help to figure out, what should I do.

Thanks!)

Labels (1)
7 Replies
hic
Employee
Employee

Well, obviously your if-condition is always FALSE:

[New-old_client] = 'Old' and [New-old_client] = 'New'

Because, in a specific record, [New-old_client] cannot be BOTH 'Old' and 'New'.

So you need to create an aggregation. For example, if the data model allows [Client_ID] to have multiple values of [New-old_client], then it could make sense to test for [Client_ID]s that have both 'Old' and 'New'. I.e. a test that runs over multiple records. That can be achieved by e.g.

Count({<[Client_ID]=P({<[New-old_client]={New}>} [Client_ID])*P({<[New-old_client]={Old}>} [Client_ID])>} [Client_ID])

where the two P() functions return New and Old, respectively, and then are intersected.

AlexWest
Contributor III
Contributor III
Author

Hello, Henric!

Thanks a lot for your answer.

No, the value from [Client_ID] has just one condition - 'Old' or 'New'.

And I need to make a code that will explane to my KPI Panel that the Filter Panel selected both values 'Old' and 'New'.

Or, I can explain that way:

I have a field [Client_ID] with unic parameters;

I have Filter with field [New-old_client] with 'New' and 'Old' parameters that match to each Client (New OR Old);

I have a KPI Panel that has to show me a count of New Clients, Old Clients, or all togather)

Could you please help me to realize this task?

hic
Employee
Employee

I am not sure I understand... Should the KPI object show New Clients, Old Clients, or all New+Old Clients, depending on your selection in the filter with field [New-old_client]?

If so, all you need to have in your KPI object to show the count is
Count(Client_ID)
The selection of clients happens automatically.

In your original code, you divide by 2, 3 and 5. If you want to do this, you can achieve it by dividing with a relevant number, like
Count(Client_ID)/If(Count(distinct [New-old_client])>1,5,1+Match([New-old_client],'New','Old'))
or something similar.

AlexWest
Contributor III
Contributor III
Author

Hello Henric,

Thanks again for your answers))

But as per I'm very very new newbie, I can't get what means the 2nd part of your code XD

If(Count(distinct [New-old_client])>1,5,1+Match([New-old_client],'New','Old'))

What means ">1,5,1+Match...?

Sorry for my stupid questions, but I'm only trying to learn this system)))

hic
Employee
Employee

In your original code you divide the Count(Client_ID) with 2, 3 or 5. This expression just generates these numbers:

If(
   Count(distinct [New-old_client])>1, 5,          // If the number of clients is > 1 Then 5
   1+Match([New-old_client],                                 // Else ...
      'New',             // If the clients is 'New' Then 1 (plus the 1 that is before 'Match') = 2
      'Old'               // If the clients is 'Old' Then 2 (plus the 1 that is before 'Match') = 3
   )
)

AlexWest
Contributor III
Contributor III
Author

Thanks so much again, Henric)

But '2', '3' and '5' numbers can be can be changed anytime)

Will this code work with such conditions?))

hic
Employee
Employee

No, this is a static definition, just to show that it is possible to have a calculated denominator. If you want to assign these numbers in a different way, you can do so.