11 Replies Latest reply: Apr 19, 2018 8:33 AM by bhuvaneshwaran m RSS

    Need Help

    bhuvaneshwaran m

      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

        • Re: Need Help
          Chanty 4u

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

          • Re: Need Help
            Chanty 4u

            its changing both for me

            try like below

            a:

            LOAD [Txn Year],

                 [Txn Month],

                 Country,

                 [Total count],

                 0 as flag

            FROM

            [C:\Users\Desktop\sample data.xlsx]

            (ooxml, embedded labels, table is Sheet1);

             

             

            B:

            LOAD [Txn Year],

                 [Txn Month],

                 Country,

                 [Total count],

                 1 as flag

            FROM

            [C:\Users\Desktop\sample data.xlsx]

            (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

              • Re: Need Help
                bhuvaneshwaran m

                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]);

              • Re: Need Help
                Jonathan Dienst

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

                 

                Need Help (Excel Databae)

                • Re: Need Help
                  praveen A

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

                  • Re: Need Help
                    praveen A

                    what is your measure field  in above script?

                     

                     

                    is [Total Txns]  your measure ?

                    • Re: Need Help
                      praveen A

                      [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


                       




                      • Re: Need Help
                        Sara Leslie

                        Hello,

                         

                        You will get better responses if you post a title with better reference of your problem. Please also see our tip for posting good questions.

                         

                        Qlik Community Tip: How to Get Answers to Your Post

                         

                        Best Regards, Sara