Disregarding date selection does not work

[nigel987]

Hi there,

I have a date field called AdPublished, and two filters on the Jahr (=Year(AdPublished)) and Monat (=Month(AdPublished)).

I want to count all AdIDs of the user in the last 31 days (Anzahl Anzeigen 31 Tage) !, disregarding any data selection. For that I use the variable =$(eAnzahlAnzeigen31TageOhneZeitDimension) which is actually:

(Count({$<AdPublished={"=AdPublished>=Date(Today()-31)"},Jahr=,Monat=>}DISTINCT AdID))

My Problem:

When I make a selection in the field Jahr or Monat, the value changes and I do not understand why. Appreciate any help!

KR, Nigel

[adamwilson · Accepted solution]

try:

Count({$<AdPublished={">=$(=Date(Today()-31))"},Jahr=,Monat=>}DISTINCT AdID)

selections on Jahr and Monat were still being applied to your set modifier "=AdPublished>=Date(Today()-31)"

[nigel987]

Thank you Adam! That's exactly what I was looking for.

[nigel987]

Hi, one follow-up question: I have a text file where I define the variable and I load the text file during reload.

SET eAnzahlAnzeigen31TageOhneZeitDimension = (Count({$<AdPublished={">=$(=Date(Today()-31))"},Jahr=,Monat=>}DISTINCT AdID));

After reload the variable appears like this:

(Count({$<AdPublished={">="},Jahr=,Monat=>}DISTINCT AdID))

Do you know how I can overcome this?

[adamwilson]

This is because of the dollar expansion $() expanding in the script rather than keeping it in the variable.

I prefer to load variables from a spreadsheet with 2 columns Name and Expression eg

Name	Expression
eAnzahlAnzeigen31TageOhneZeitDimension	Count({$<AdPublished={"=AdPublished>=Date(Today()-31)"},Jahr=,Monat=>}DISTINCT AdID)

and use the below script to load them in which overcomes the expanding of $() in the script:

Variables: 
LOAD  
     Name, 
     Expression 
FROM 
Variables.xlsx 
(ooxml, embedded labels, table is Sheet1); 

For i = 0 to NoOfRows('Variables')-1 
     LET vVariableName = Peek('Name',i,'Variables'); 
     LET $(vVariableName) = Peek('Expression',i,'Variables');       
NEXT 

DROP Tables Variables; 
i=NULL(); 
vVariableName=NULL();