Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)"
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)"
Thank you Adam! That's exactly what I was looking for.
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?
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();