Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
helge_jorg
Creator
Creator

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

Can someone please help me?

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:

MAPPING LOAD 

rowno() as Month,

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

AUTOGENERATE (12);

    

Temp:

Load

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:

LOAD

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

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

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

    

MasterCalendar:

Load

  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;

16 Replies
sunilkumarqv
Specialist II
Specialist II

can you share sample data

helge_jorg
Creator
Creator
Author

A samlpe app is now attached to my first post.

helge_jorg
Creator
Creator
Author

A sample app is now attached to my first post.

Neymar_Jr
Creator II
Creator II

Hi PFA,

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

Thanks,

RT

bwisealiahmad
Partner - Specialist
Partner - Specialist

Hi,


Does this not work?

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

You have checked that the Master Calendar is functioning and the Date field is an actual date field?

Other links:

Qlikview Date Functions RFB 135 - YouTube

Get the Dates Right

Dates in Set Analysis

The Date Function

I think it would help if you could share a couple rows of Data...

Best,

Ali A

bwisealiahmad
Partner - Specialist
Partner - Specialist

Rajesh is correct.

If you in the script change:

Vaerdata:

LOAD

    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

helge_jorg
Creator
Creator
Author

Thanks!!

I will change it to DateFormat!