Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
This is great, let me look at this now....
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.
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.
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);
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
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);
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.
Any solution or suggest the helpful condition
Any suggestion or helpful guidance which can use and check it...