Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
When I select the year or month in the filter pane the result should be sheet1 total and sheet 2 total separately. Like below
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
do you have link between to sheets? then it will work. if there is no relation it will not work
how to create the link between two sheets.?
i have 2 sheets here.
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
Please do not cross post questions. It cause clutter and messes up the responses you may get.
you can rename the field "Total count " and use it seperately.you will reach ur requirement
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]);
what is your measure field in above script?
is [Total Txns] your measure ?
total txn
[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