16 Replies Latest reply: Aug 1, 2018 4:40 AM by Helge Jørgensen

# Set analysis - sum of last 30 days

Qlik experts,

I do know that this question has been asked several times here on the community, but I just can’t get it to work.

I would like to use a KPI to show a measure of the last 30 days. I have used this expression:

sum({<ElementID= {'RR'}, Dato = {">\$(=Dato(Today()-30))"}>}[Vaerdata.Verdi])

Dato is my date filed (Norwegian)

The problem is that the expression does not show the last 30 days, but the entire record of data (several years).

And is there a post or blog where I can read more about how to use Date-function in Qlik sense?

Cheers

Helge

Here is the Calander-script that I use:

QuartersMap:

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Min(Dato) as minDate,

max(Dato) as maxDate

Resident Vaerdata;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

\$(varMinDate) + Iterno()-1 As Num,

Date(\$(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While \$(varMinDate) + IterNo() -1 <= \$(varMaxDate);

MasterCalendar:

TempDate AS Dato,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

• ###### Re: Set analysis - sum of last 30 days

H,

please try below ,also let us know Dato format.

sum({<ElementID= {'RR'}, Dato = {">=\$(=Dato (max(today())-30, 'DD/MM/YYYY'))"}>}[Vaerdata.Verdi])

• ###### Re: Set analysis - sum of last 30 days

Dato-format is my date format (Norwegian). I have updated the post with more information.

Your expression gives the sum of the enitre record (several years)

Thanks

HJ

• ###### Re: Set analysis - sum of last 30 days

Hi,

Should it not be -

sum({<ElementID= {'RR'}, Dato = {">\$(=Date(Today()-30))"}>}[Vaerdata.Verdi])  ?

Thanks,

RT

• ###### Re: Set analysis - sum of last 30 days

Sorry, it does not work - it gives my a value of 0.

Thanks

HJ

• ###### Re: Set analysis - sum of last 30 days

Hi!

Is Dato your date field? Maybe go like this:

sum({<ElementID={'RR'}, Dato={">\$(=Date(Today()-30))"}>}[Vaerdata.Verdi])

• ###### Re: Set analysis - sum of last 30 days

Dato is my date filed.

Your expression gives me a value of 0.

Thanks

HJ

• ###### Re: Set analysis - sum of last 30 days

Can you share a sample app with us?

• ###### Re: Set analysis - sum of last 30 days

A samlpe app is now attached to my first post.

• ###### Re: Set analysis - sum of last 30 days

But it is not

sum({<ElementID= {'RR'}, Dato = {">\$(=Date(Today()-30))"}>}[Vaerdata.Verdi])

?

• ###### Re: Set analysis - sum of last 30 days

Sorry, byt your expression gives me a value of 0.

• ###### Re: Set analysis - sum of last 30 days

can you share sample data

• ###### Re: Set analysis - sum of last 30 days

A sample app is now attached to my first post.

• ###### Re: Set analysis - sum of last 30 days

Hi PFA,

Your field is in TimestampFormat, I guess you will have to change it to DateFormat.

Thanks,

RT

• ###### Re: Set analysis - sum of last 30 days

Rajesh is correct.

If you in the script change:

Vaerdata:

Date(Floor(Dato)) AS Dato,

ElementID,

Vaerdata.Verdi

FROM [lib://Data/Data_.xlsx]

(ooxml, embedded labels, table is Sheet1);

That will remove the timestamp and from there I believe (which removes the trailing time zeros and will look much better...)

Sum({<ElementID = {'RR'}, [Dato] = {">=\$(=Date(Today()-30,'DD.MM.YYYY'))"}>} [Vaerdata.Verdi])

Or else if you want to keep the trailing TimeStamp format.

Sum({<ElementID = {'RR'}, [Dato] = {">=\$(=TimeStamp(Today()-30))"}>} [Vaerdata.Verdi])

Or the other suggestions above should work.

Best,

Ali A

• ###### Re: Set analysis - sum of last 30 days

Thanks!!

I will change it to DateFormat!