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: 
vlakeshr
Creator
Creator

Re: How can add "If and" condition in pivot table in Qliksense

4th formula

If (Client_type=Individual and country = 'A', (Sum(VALUES)-(value))/sum(VALUES),

If (Client_type=Individual and country = 'B', (Sum(VALUES)-(value))/sum(VALUES),

NULL()))

I used above formula in pivot table but I am getting negative values in % column, that is not correct. somebody help me to fix that issue ..I want to  fix this issue in the next 48 hours.

Awiting Solution on  this error..

Thanks in advance for quick response

19 Replies
sunny_talwar

Random data is good... but where exactly are you carrying out this calculation in a chart? Based on the random data, would you be able to let me know what is the expected output? Just may be update the Excel to create a similar chart or calculation in Excel and we can take the rest.

sunny_talwar

This is great, let me look at this now....

vlakeshr
Creator
Creator
Author

I have mentioned the calculation formula as

Formula for calculation is using as

(Total Value - Value for DE) / Total Value

In the qliksense and in Pivot table.

Based on country selection need to get correct calculation for %. Please try it and let me know if you get any solution..

I used If condition, let me know can we use any other formula for this requirement.

Please suggest me.

sunny_talwar

I am not still not sure I understand it completely... what is the link between this

If (Client_type=Individual and country = 'A', (Sum(VALUES)-(value))/sum(VALUES),

If (Client_type=Individual and country = 'B', (Sum(VALUES)-(value))/sum(VALUES),

NULL()))

and this

(Total Value - Value for DE) / Total Value

Again, it would help to see the expected numbers in Excel file based on your random numbers.

vlakeshr
Creator
Creator
Author

Clarification is that

If client type is'Ind' and country is A then (Sum(VALUES)-(value))/sum(VALUES)


and second condition for B country


If client type is'Ind' and country is A then (Sum(VALUES)-(value))/sum(VALUES)


U can change VALUES to B VALUES..


I am using cross table because data is in the mentioned format as per attached excel sheet.


So I used

Cross Table ( Month, Values,Column no.)

You can use any name instead of Value..I have used Value.


Table_b:

crosstable(Month,  Value  ,  5)

LOAD
    DE,
    "FCC 20",
    CATEEGORY,
    "DQ DIM",
    Country,

"42711",

    "42742",

    "42773",

    "42801",

    "42832",

    "42862",

    "42893",

    "42923",

    "42954",

    "42985",

    "43015",

    "43046",

    "43076"


FROM [lib://HK/HK_Sales_1.xlsx]
(ooxml, embedded labels, table is Sheet1);

sunny_talwar

I really wish to help, but unless I understand what you need, it would be impossible for me to help you my friend.... May be someone else might be able to help if they can understand.

All the best

Best,

Sunny

vlakeshr
Creator
Creator
Author

I tell you from starting..

I have uploaded data for below three tables , I want % of calculation for all country which will reflect in the

% column of pivot table.

Calculation formula is

(Total_SGH - Database_SGH) / (Total_SGH

Used in measure :

1st:

if([Client Type] ='Individual' or COUNTRY = 'SGH', (sum(Values) - (Value))/(sum({1}Values)),

if([Client Type] ='Individual' or COUNTRY = SGHJ',(sum(SGHJValues) - (Value))/(sum({1}SGHJValues)),

if([Client Type] ='Individual' or COUNTRY = 'ARG',(sum(ARGValues) - (Value))/(sum({1}ARGValues)),

Null())))

2nd:

if([Client Type] ='Individual' AND COUNTRY = 'SGH', (sum(Values) - (Value))/(sum({1}Values)),

if([Client Type] ='Individual' AND COUNTRY = SGHJ',(sum(SGHJValues) - (Value))/(sum({1}SGHJValues)),

if([Client Type] ='Individual' AND COUNTRY = 'ARG',(sum(ARGValues) - (Value))/(sum({1}ARGValues)),

Null())))

I hv tried  If condition for calculation of % for three country but I am getting negative value of one of the country and wrong calculation of 2nd country and only one country calculation is getting correct.


Database_SGH:
CrossTable (Month, Value, 7)


LOAD
    "Data Elements",
    "FCC Top 20",
    "Client Type",
    "DQ Dimension",
    COUNTRY,
    CLIENT_ROLE,
    DE_KEY,
    "12/31/2016",
    "1/31/2017",
    "2/28/2017",
    "3/31/2017",
    "4/30/2017",
    "5/31/2017",
    "6/30/2017",
    "7/31/2017",
    "8/31/2017",
    "9/30/2017",
    "10/31/2017",
    "11/30/2017",
    "12/31/2017"
   
FROM [lib://AttachedFiles/Data Profiling Template.xlsx]
(ooxml, embedded labels, table is Database_SGH);


Total_SGH:
CrossTable (Month, Values, 1)

LOAD
    "Client Type" as SGH_Client_Type ,
    "12/31/2016",
    "1/31/2017",
    "2/28/2017",
    "3/31/2017",
    "4/30/2017",
    "5/31/2017",
    "6/30/2017",
    "7/31/2017",
    "8/31/2017",
    "9/30/2017",
    "10/31/2017",
    "11/30/2017",
   "12/31/2017"
  
FROM [lib://AttachedFiles/Data Profiling Template.xlsx]
(ooxml, embedded labels, table is Total_SGH);


Database_SGHJ:
CrossTable (Month, Value, 7)

LOAD
    "Data Elements",
    "FCC Top 20",
    "Client Type",
    "DQ Dimension",
     COUNTRY,
    CLIENT_ROLE,
    DE_KEY ,
    "12/31/2016",
    "1/31/2017",
    "2/28/2017",
    "3/31/2017",
    "4/30/2017",
    "5/31/2017",
    "6/30/2017",
    "7/31/2017",
    "8/31/2017",
    "9/30/2017",
    "10/31/2017",
    "11/30/2017",
    "12/31/2017"

  

FROM [lib://AttachedFiles/Data Profiling Template.xlsx]

(ooxml, embedded labels, table is [Database-SGHJ]);

Total_SGHJ:
CrossTable (Month, SGHJValues, 1)

LOAD
    "Client Type"as SGHJ_CLient_Type,
    "12/31/2016",
    "1/31/2017",
    "2/28/2017",
    "3/31/2017",
    "4/30/2017",
    "5/31/2017",
    "6/30/2017",
    "7/31/2017",
    "8/31/2017",
    "9/30/2017",
    "10/31/2017",
    "11/30/2017",
    "12/31/2017"

FROM [lib://AttachedFiles/Data Profiling Template.xlsx]
(ooxml, embedded labels, table is Total_SGHJ);

Database_ARG:
CrossTable (Month, Value, 7)

LOAD
    "Data Elements",
    "FCC Top 20",
    "Client Type",
    "DQ Dimension",
     COUNTRY,
    CLIENT_ROLE,
    DE_KEY,
    "12/31/2016",
    "1/31/2017",
    "2/28/2017",
    "3/31/2017",
    "4/30/2017",
    "5/31/2017",
    "6/30/2017",
    "7/31/2017",
    "8/31/2017",
    "9/30/2017",
    "10/31/2017",
    "11/30/2017",
    "12/31/2017"
  
FROM [lib://AttachedFiles/Data Profiling Template.xlsx]
(ooxml, embedded labels, table is [Database-ARG]);

Total_ARG:
CrossTable (Month, ARGValues, 1)

LOAD
   "Client Type" as ARG_Client_Type,
    "12/31/2016",
    "1/31/2017",
    "2/28/2017",
    "3/31/2017",
    "4/30/2017",
    "5/31/2017",
    "6/30/2017",
    "7/31/2017",
    "8/31/2017",
    "9/30/2017",
    "10/31/2017",
    "11/30/2017",
    "12/31/2017"
   
FROM [lib://AttachedFiles/Data Profiling Template.xlsx]
(ooxml, embedded labels, table is Total_ARG);

vlakeshr
Creator
Creator
Author

anybody have any other option for calculation which will help to get correct % calculation, please share other function or  condition which can use to fulfill the correct calculation requirement.

vlakeshr
Creator
Creator
Author

Any solution or suggest the helpful condition

vlakeshr
Creator
Creator
Author

Any suggestion or helpful guidance which can use and check it...