- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi PFA,
Your field is in TimestampFormat, I guess you will have to change it to DateFormat.
Thanks,
RT
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
H,
please try below ,also let us know Dato format.
sum({<ElementID= {'RR'}, Dato = {">=$(=Dato (max(today())-30, 'DD/MM/YYYY'))"}>}[Vaerdata.Verdi])
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Should it not be -
sum({<ElementID= {'RR'}, Dato = {">$(=Date(Today()-30))"}>}[Vaerdata.Verdi]) ?
Thanks,
RT
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi!
Is Dato your date field? Maybe go like this:
sum({<ElementID={'RR'}, Dato={">$(=Date(Today()-30))"}>}[Vaerdata.Verdi])
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
But it is not
sum({<ElementID= {'RR'}, Dato = {">$(=Date(Today()-30))"}>}[Vaerdata.Verdi])
?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sorry, it does not work - it gives my a value of 0.
Please see original post - I have updated it with more information
Thanks
HJ
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Dato is my date filed.
Your expression gives me a value of 0.
Please see original post - I have updated it with more information
Thanks
HJ
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sorry, byt your expression gives me a value of 0.
Please see original post - I have updated it with more information
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Can you share a sample app with us?
- « Previous Replies
-
- 1
- 2
- Next Replies »