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 :



      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





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


      PS : sorry for my English