Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

msbhuvanesh003
New Contributor II

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
sureshqv
Esteemed Contributor III

Re: Need Help

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

11 Replies
sureshqv
Esteemed Contributor III

Re: Need Help

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

msbhuvanesh003
New Contributor II

Re: Need Help

how to create the link between two sheets.?

i have 2 sheets here.

sureshqv
Esteemed Contributor III

Re: Need Help

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

MVP
MVP

Re: Need Help

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

Re: Need Help

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

msbhuvanesh003
New Contributor II

Re: Need Help

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

Re: Need Help

what is your measure field  in above script?

is [Total Txns]  your measure ?

msbhuvanesh003
New Contributor II

Re: Need Help

total txn

chinnuchinni
Contributor III

Re: Need Help

[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





Community Browser