Table Chart expression

[inam]

Hello All,

I want to add one column in my existing table but I am getting error.

 with the help of below sheet i need to add Total column in my table

sheet:-

My Existing Table :- below is my existing table here i want to add one more column sales Budget (Total).

Expected Output:-

for exmaple

Thailand :- it is destination (unit) column

source:- B2B & XML

B2B is 18,00,000 & XML is 20,30,000   Total is 38,30,000 so i wnat to add Total as ( sales Budget ) in My table.

[PrashantSangle]

Can you provide some more info about your query?

like

1: how you load that file qliksense? sharing your script will be helpful.

2: What are the expression you used? 

3: What are the things you tried? what is the error you are getting?

regards,

Prashant Sangle

[inam]

 how you load that file Qliksense? sharing your script will be helpful.

:-  I have 2 sheets 1 is below 

LOAD No.,
[Agent Name],
[Agent Country],
[Agent City],
[Bkg Type],
[File No.],
[O/S Ref.],
[Destination Country],
UNIT,
[Destination City],
[Hotel/Transport/Place],
[Room Type/Service Name],
[Meal Plan],
[Guest Name],
Nationality,
[Total Pax],
[Service Status],
[Booking Status],
[Check-In],
Month,
[Check-out],
[No. Of Units],
[No. Of Nights],
[Room Nights],
[Invoice Currency],
Invoice,
[Invoice Default Currency],
[Cost Currency],
Cost,
[Cost Default Currency],
[Source of booking],
[Bkg Source],
[Handled By],
[Time of Create Service Booking],
[User Create],
[Username Create],
Supplier,
Margin,
margin%,
[AT Units],
[Actual Sales],
[Actual Cost],
[Actual Margin],
[Actual Margin %],
[Supplier Source],
Bookings,
[Contribution on Sales],
[Sales (Budget)],
[AT Units1],
AU,
AV
FROM

and 2nd sheet is (budget sheet i share  on the top)

LOAD Destination,
Source,
JAN,
FEB,
MAR,
APR,
MAY,
JUN,
JUL,
AUG,
SEP,
OCT,
NOV,
DEC,
TOTAL 

What are the expression you used? 

:- in expression I add normally TOTAL(Present in budget sheet) but its not getting any answer when i add TOTAL in expression the total pax  and all other field total amount is changed.

What are the things you tried? what is the error you are getting?

:- 1st i load both sheet using left join and in expression tab add only TOTAL but not getting any answer.

     you have any solution how i add Sales Budget column in my existing table? i need expression.

[PrashantSangle]

in your 1st table you have Unit field which contain your destination address. So, you have to rename the field Destination from 2nd table

so your 2nd table will became

LOAD Destination as Unit,
Source,
JAN,
FEB,
MAR,
APR,
MAY,
JUN,
JUL,
AUG,
SEP,
OCT,
NOV,
DEC,
TOTAL 

Then in your expression just use sum(TOTAL)

Regards,

Prashant Sangle

[inam]

Hii  Prashant Thank for reply .

i apply the same but getting answer is Different  also why Total Pax and all others field total values is changed?

below is snapshot.

i load this below:-

temp:
LOAD No.,
[Agent Name],
[Agent Country],
[Agent City],
[Bkg Type],
[File No.],
[O/S Ref.],
[Destination Country],
UNIT,
[Destination City],
[Hotel/Transport/Place],
[Room Type/Service Name],
[Meal Plan],
[Guest Name],
Nationality,
[Total Pax],
[Service Status],
[Booking Status],
[Check-In],
Month,
[Check-out],
[No. Of Units],
[No. Of Nights],
[Room Nights],
[Invoice Currency],
Invoice,
[Invoice Default Currency],
[Cost Currency],
Cost,
[Cost Default Currency],
[Source of booking],
[Bkg Source],
[Handled By],
[Time of Create Service Booking],
[User Create],
[Username Create],
Supplier,
Margin,
margin%,
[AT Units],
[Actual Sales],
[Actual Cost],
[Actual Margin],
[Actual Margin %],
[Supplier Source],
Bookings,
[Contribution on Sales],
[Sales (Budget)],
[AT Units1],
AU,
AV
FROM
[D:\QlikView_inam\data\At Group\vi. B2B Online Reports & vii. Sourcing Report\DIFFRENT\AT Group Online Booking Report.qvd](qvd);

Left join(temp)
LOAD Destination as UNIT,
Source,
JAN,
FEB,
MAR,
APR,
MAY,
JUN,
JUL,
AUG,
SEP,
OCT,
NOV,
DEC,
TOTAL AS Total2
FROM
[D:\QlikView_inam\data\At Group\vi. B2B Online Reports & vii. Sourcing Report\DIFFRENT\budget.xlsx]
(ooxml, embedded labels, table is Sheet1);

[MayilVahanan]

Hi

I think, your budget excel sheet has duplicate values or its for 2 year? What is the link between 2 tables?

Instead of join, if you keep separately, 2 tables are join auto based on common field [Key field], hope it doesn't change "Total" values

[inam]

Hello ,

Thanks MayilVahanan  working fine now😊

i have one another doubt can you please help.

I need this Excel  formula in QlikView. 

excel formula :-   =IFERROR(1/COUNTIF([File No.],[@[File No.]]),0)

I am trying  this  IF(COUNT([File No.]) > 0, 1 / COUNT({< [File No.] = {"$(= [File No.])"} >}), 0)  but its not work.

[Chanty4u]

Try this

=If(Count([File No.]) > 0, 1 / CountDistinct({< [File No.] = {"$(= [File No.])"} >} [File No.]), 0)

[inam]

Hello 

I apply but getting error. how I resolve this .