Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cathg2000
New Contributor

Qlik Sense - using set expressions

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.

1 Solution

Accepted Solutions
Employee
Employee

Re: Qlik Sense - using set expressions

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.

6 Replies
Employee
Employee

Re: Qlik Sense - using set expressions

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')

cathg2000
New Contributor

Re: Qlik Sense - using set expressions

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
Contributor II

Re: Qlik Sense - using set expressions

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.

Employee
Employee

Re: Qlik Sense - using set expressions

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
New Contributor III

Re: Qlik Sense - using set expressions

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

cathg2000
New Contributor

Re: Qlik Sense - using set expressions

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.