Qlik Sense - using set expressions

[Anonymous]

Hi,

I've set up a text field and I am trying to calculate a total where the MonthYear field equals the value in PreviousMonthYear field.  i.e. I've set up a table that loads a column called PreviousMonthYear which keeps track of what the previous month is (format is MMM-YYYY).

I've tried using a whole heap of expressions but I either get a blank field or a 0.

One of the expressions is:

Sum({1<MonthYear={ $(=PreviousMonthYear) }>} TOT_INC)

Any ideas on what I'm doing wrong and why it won't calculate?

I've also tried

Sum( {1<MonthYear = {"Aug-2017"}>} TOT_INC)

which works fine.

So I set up a variable to take the currently selected value but I can't get the following to work:

Sum( {1<MonthYear = {"$(vPreviousMonthYear)"}  >} TOT_INC)

Thanks in advance.

[ToniKautto]

I would suggest that you start by exposing the variable vPreviousMonthYear, by applying the measure ='$(vPreviousMonthYear)' in a title, table measure or text object. Does the result have the same format as your static value Aug-2017?

To reformat the variable, enclose your variable definition in Date(variable_definition, 'MMM-YYYY')

[Anonymous]

Hi Toni,

I've set up some Text boxes which display:

=MonthYear

which displays Aug-2017

and

=PreviousMonthYear

which displays Jul-2017

but I can't get the SUM expression to use these values.

How do I take the text value that I get in the text boxes and incorporate it into the SUM function?

[sumanta12]

Hi,

First take a text object and in the expression put =$(vPreviousMonthYear) and see in which format  month year value is coming then put that variable in the expression.

[ToniKautto · Accepted solution]

My assumption is that PreviousMonthYear and MonthYear are fields in your data model. If that is the case then this expression could possibly work.

Sum({1<MonthYear={"$(=PreviousMonthYear)"}>} TOT_INC)

It would be easier to advice further if you can provide a simple sample app file.

[adj29block]

Hello,

Please translate French - English

Pour utiliser des dates je vous propose d'utiliser le plus simple les variables.

Comment :

Exemple:

Dans le script sous les Set on ajoute

Let vLastReload = Date(now(), 'DD MMMM YYYY hh:mm');

/*CurrentDate*/

Let vToday = '=vCurrentDate'; /*Table Sales, Field OrderDate 26/06/2014 sans cela il renvoi la date d’aujourd’hui 2017*/

// Let vToday= Date(Today(),'DD MMMM YYYY'); /* Date courante */

let vCurrentYear  = '=max(Year)';

let vCurrentDate  = '=max(OrderDate)';

let vCurrentMonth  = '=month(max(OrderDate))';

let vCurrentDay   = '=day(max(OrderDate))';

/*PreviousDate*/

let vPreviousYear  = '=vCurrentYear-1';

let vPreviousDate = '=date(addyears(max(OrderDate),-1))';

let vPreviousMonth  = '=month(addmonths(max(OrderDate),-1))';

Let vPreviousDay = '=vCurrentDay-1';

Attention au respect de l'écriture

Ensuite les sommes pour la date courante dans un KPI un pour Year, un pour Month, un pour Day et un histogramme avec une Dimension Hiérarchique : Year, Month, OrderDate... et Expression : Sum(Sales)

Le titre pour YTD est ='Year : ' & vCurrentYear, pour MTD ='Month : '& vCurrentMonth, pour DTD=' Day : ' & vCurrentDay.

YTD =SUM({<Year={'$(vCurrentYear)'},Month=,OrderDate=>}Sales)

MTD =SUM({<Year={'$(vCurrentYear)'},Month={'$(vCurrentMonth)'},OrderDate=, Day=>}Sales)

DTD =SUM({<Year={'$(vCurrentYear)'},Month={'$(vCurrentMonth)'},OrderDate=, Day={'$(vCurrentDay)'}>}Sales)

Pour PreviousDate on remplace vCurrentDate par vPreviousYear, vPreviousMonth et Si on veut Day-1 vPreviousDay.

Month=,OrderDate=,Day= vérouillent la date à l'année, au mois, à day

Faites la vérification avec un tableau croisé Dimension :Year, Month et Expression : Sum(Sales) vous verrez si des dates ont une somme=0

Enfin pour savoir où vous en êtes :

Dans une Zone texte :

='Order Date - ' &' '& If((SUM({<Year={'$(vCurrentYear)'},Month={'$(vCurrentMonth)'},[Invoice Date]=,OrderDate=, Day={'$(vCurrentDay)'}>}Sales)<=0),OrderDate &' '& 'No Sales',

If((SUM({<Year={'$(vCurrentYear)'},Month={'$(vCurrentMonth)'},[Invoice Date]=,OrderDate=, Day={'$(vCurrentDay)'}>}Sales)>0),GetFieldSelections(OrderDate)))

Autre : Pour une période N et N-1 avec Field = date

Code Pour la période N :

sum({$ <date={'>=$(=addmonths(min(date))) <=$(=addmonths(max(date)))'}>} valeur)

Libelle :

='Sélection (entre ' & date(Min(date)) & ' et ' & date(Max(date)) & ')'

Code Pour la période N-1 :

sum({$ <date={'>=$(=addmonths(min(date), -12)) <=$(=addmonths(max(date), -12))'}>} valeur)

Libellé :

='Sélection N-1 (entre ' & addmonths(min(date), -12) & ' et ' & addmonths(max(date), -12) & ')'

Un conseil pour Qlik Sense UTILISER MASTER CALENDAR et supprimer AUTOCALENDAR:

Salutations

[Anonymous]

Thanks!  This reply got me thinking more about the formats of the fields I was using.  I've since changed my fields to make sure they are dates and always reference them in the date format.

1.  Date1 now comes in as a date...

LOAD

    Date([Month],'MMM-YYYY') as "Date1",

...

2.  I've set vCurrentMonth to =Date(Max(Date1),'MMM-YYYY')

3.  Now,  Sum( {$<Date1={"$(vCurrentMonth)"}>} [TOT_INC] ) works for me and I just display the Date1 field in

Date(Date1,'MMM-YYYY') format when I need to.