12 Replies Latest reply: Dec 21, 2015 9:34 AM by bruno bertels RSS

    GetFieldSelections and Dynamic Title and rangesum()

      Hello Everybody,

       

      I'm a student, and i'm a newbie. i discovered qliksense desktop yesterday.

       

      i dont find in forum my answer. so i will try to post here and i hope to have answer

       

      My input file is Excel. In this file i have worksheet  "calendrier" wich contains :

       

      date(dd-mm-yyyy) / year / month .

       

      In Load script i did :

       

      LOAD     idTemps,     date,     jour,     mois,     annee,     li_mois,     (annee&'-'&if(len(trim([date]))>0, 'S'&num(ceil(month([date])/6)))) as annee_semestre,     (annee&'-'&li_mois) as annee_mois,     (annee&'-'&if(len([date])>0, 'T' & ceil(month([date])/3))) as annee_trimestre       FROM [lib://ProtoX/Dimensions.xlsx] (ooxml, embedded labels, table is Dim_Temps) WHERE annee = Year(Today())-1 or annee = year(Today()) ;

       

      I have 3 problems :

      1st exigence of my client is :  would like to create 1 dimension called "Periode" and values on this dimension :

       

       

      2015-01 2015-02 2015-03 2015-04 2015-05 2015-06 2014-01 2014-02 2014-03 2014-04 2015-Q1 2015-Q2 2015-S1 2015-S2 2014-S1 2014-S2

      This dimension its for use in "filter"

       

      In your point of view its possible to create this specific value ? i tried but it was not a success


      2nd : I try to have dynamic title


      For exemple if my client filtered on ' 2014-04,2014-03' i would like to have ' analyse on year : 2014 and on month : April and March


      'Analyse : ' & Subfield(GetFieldSelections(annee_mois),'-',1) & ' ' & subfield(concat(GetFieldSelections(annee_mois),','),'-',2)

       

      it's ok for year but not for month and i dont understand why ?

       


      2nd :Crosstab


      In my crosstab my client wants to see specific order, so i used :


      WildMatch(li_Magasin,'Lyon','*')

       

      Now i would like to insert a row "TOTAL" above my first row. its cumulative sum by "li_magasin"*

       

      i saw on forum this fonction

       

      rangesum()

       

       

      I hope you can help me, thank you for your time.

       

      PS : sorry for my English