5 Replies Latest reply: Sep 5, 2014 7:43 PM by Marco Wedel RSS

    How to do Multiple Cross table based

    M srinivas

      Hi Dear all,

      Please find the attached application here i need cross table.

      i want different dimession like Region, Sale,Items,FYmonth,Month,Quarter,Sales...like this way.

      Based on my excel i tried but i can't able to do.PLEASE HELP ON THIS.

       

      Regards

      Munna

        • Re: How to do Multiple Cross table based
          Jonathan Poole

          Hi. i took a stab at it.  The main question is that your date periods cover different time periods with different granularity.  Current month has days, prior 12 months has months and quarterly summaries,  prior years only have years.

           

          i just loaded all 'as is' so in a single list box you can pick a prior year and a day from the current month for example.

           

          You'll need to think through how these values are calculated before trying to convert them to dates for example. 

           

          But the script technique should help.  One of the things i did was convert the days to dates and leave the rest.

          • Re: How to do Multiple Cross table based
            Marco Wedel

            Hi,

             

            not finished, but maybe helps:

            QlikCommunity_Thread_132000_Pic1.JPG.jpg

            tabInput:
            CrossTable (FieldNameTxt, FieldValue, 3)
            LOAD * FROM [http://community.qlik.com/servlet/JiveServlet/download/602142-123487/Daily1.xlsx] (ooxml, embedded labels, table is Sheet1, filters(
            Remove(Row, RowCnd(CellValue, 3, StrCnd(contain, 'Total'))),Replace(1, top, StrCnd(null)),Replace(2, top, StrCnd(null))));
            
            Right Join
            LOAD Distinct
              FieldNameTxt,
              If(WildMatch(PurgeChar(FieldNameTxt,'[]'), 'FY (??-??)', '?????.000000'),
              Pick(WildMatch(PurgeChar(FieldNameTxt,'[]'), 'FY (??-??)', '?????.000000'),
              Dual(PurgeChar(FieldNameTxt,'[]'), Mid(FieldNameTxt,6,2)),
              Date(PurgeChar(FieldNameTxt,'[]'))
              ),PurgeChar(FieldNameTxt,'[]')) as FieldName,
              If(WildMatch(PurgeChar(FieldNameTxt,'[]'), 'FY (??-??)', '?????.000000'),
              Pick(WildMatch(PurgeChar(FieldNameTxt,'[]'), 'FY (??-??)', '?????.000000'),
              'FY',
              'Date'
              ),'other') as Dimension
            Resident tabInput
            Where FieldNameTxt<>'Total';
            

             

             

            regards

             

            Marco

            • Re: How to do Multiple Cross table based

              Attached is the QVW document. See if this is something you are looking for?