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: 
msbhuvanesh003
Contributor III
Contributor III

Need Help

Dear Experts,

Platform: Qlik Sense

Database: Excel Feed

I have a excel file for database, in this excel file having 2 sheets. Both the sheets having Year and Month columns. When I select the year or month in the filter pane the result should be sheet1 count and sheet 2 counts separately.

but i m getting while selecting year from filter pane only sheet 1 total only changed.

i want both total should be changed while selecting year from filter pane.

  1. Ex.

When I select the year or month in the filter pane the result should be sheet1 total and sheet 2 total separately. Like below

Capture.JPG

1 Solution

Accepted Solutions
Chanty4u
MVP
MVP

its changing both for me

try like below

a:

LOAD [Txn Year],

     [Txn Month],

     Country,

     [Total count],

     0 as flag

FROM

(ooxml, embedded labels, table is Sheet1);

B:

LOAD [Txn Year],

     [Txn Month],

     Country,

     [Total count],

     1 as flag

FROM

(ooxml, embedded labels, table is Sheet2);

frontend kpi

=sum({<flag={0}>}[Total count])

=sum({<flag={1}>}[Total count])

take year and month filter and check

View solution in original post

10 Replies
Chanty4u
MVP
MVP

do you have link between to sheets?  then it will work.  if there is no relation it will not work

msbhuvanesh003
Contributor III
Contributor III
Author

how to create the link between two sheets.?

i have 2 sheets here.

Chanty4u
MVP
MVP

its changing both for me

try like below

a:

LOAD [Txn Year],

     [Txn Month],

     Country,

     [Total count],

     0 as flag

FROM

(ooxml, embedded labels, table is Sheet1);

B:

LOAD [Txn Year],

     [Txn Month],

     Country,

     [Total count],

     1 as flag

FROM

(ooxml, embedded labels, table is Sheet2);

frontend kpi

=sum({<flag={0}>}[Total count])

=sum({<flag={1}>}[Total count])

take year and month filter and check

jonathandienst
Partner - Champion III
Partner - Champion III

Please do not cross post questions. It cause clutter and messes up the responses you may get.

Need Help (Excel Databae)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
chinnuchinni
Creator III
Creator III

you can rename the field "Total count " and use it seperately.you will reach ur requirement

msbhuvanesh003
Contributor III
Contributor III
Author

i m getting sheet 1 total 0 and sheet 2 getting correct value.

f.y.i

[Raw Data]:

LOAD

[Txn Date Year],

[Txn Date Month],

[Sending Agent Country Code],

[Service Provider Code] AS [Raw Data.Service Provider Code],

[Drawee Bank Country Name],

[Drawee Bank Code],

[Drawee Bank Branch Code],

[Drawee Bank Name],

[DeliveryChannel] AS [Raw Data.DeliveryChannel],

[Payout Ccy Code] AS [Raw Data.Payout Ccy Code],

[Total Txns] AS [Raw Data.Total Txns],

[Payin Amount] AS [Raw Data.Payin Amount],

[Payout Amount] AS [Raw Data.Payout Amount],

[Count],

[Concentrate1],

[Concentrate2],

[Concentrate3],

[Concentrate4],

    0 as flag,

APPLYMAP( '__countryCodeIsoTwo2Polygon', UPPER([Sending Agent Country Code]), '-') AS [Raw Data.Sending Agent Country Code_GeoInfo],

APPLYMAP( '__countryCodeIsoThree2Polygon', APPLYMAP( '__countryName2IsoThree', LOWER([Drawee Bank Country Name])), '-') AS [Raw Data.Drawee Bank Country Name_GeoInfo]

FROM [lib://CBRS (uaeexchange_bhuvaneshwaran.m)/Bank Performance Dashboard.xlsx]

(ooxml, embedded labels, table is [Raw Data]);

[XM Data]:

LOAD

[Txn Year],

[Txn Month],

[Country Name],

[DeliveryChannel] AS [XM Data.DeliveryChannel],

[Destination Country Name],

[Service Provider Code] AS [XM Data.Service Provider Code],

[Payout Ccy Code] AS [XM Data.Payout Ccy Code],

[Delivery Option Desc],

[Payin Amount] AS [XM Data.Payin Amount],

[Payout Amount] AS [XM Data.Payout Amount],

[Total Txns] AS [XM Data.Total Txns],

[Concentrate],

    1 as flag,

APPLYMAP( '__countryCodeIsoThree2Polygon', APPLYMAP( '__countryName2IsoThree', LOWER([Country Name])), '-') AS [XM Data.Country Name_GeoInfo],

APPLYMAP( '__countryCodeIsoThree2Polygon', APPLYMAP( '__countryName2IsoThree', LOWER([Destination Country Name])), '-') AS [XM Data.Destination Country Name_GeoInfo]

FROM [lib://CBRS (uaeexchange_bhuvaneshwaran.m)/Bank Performance Dashboard.xlsx]

(ooxml, embedded labels, table is [XM Data]);

chinnuchinni
Creator III
Creator III

what is your measure field  in above script?

is [Total Txns]  your measure ?

msbhuvanesh003
Contributor III
Contributor III
Author

total txn

chinnuchinni
Creator III
Creator III

[Total Txns] AS [Raw Data.Total Txns], in 1st sheet




[Total Txns] AS [[Raw Data.Total Txns], in second sheet



sum([Raw Data.Total Txns])   for 1st


and


sum([Raw Data.Total Txns])   for 2nd




if you will not rename the total txn


sum({<flag = {0}>}[Total Txns])  for 1st




sum({<flag = {1}>}[Total Txns])    for 2nd