Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

helge_jorg
New Contributor III

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;

1 Solution

Accepted Solutions
rajeshthakur292
Contributor II

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

16 Replies
sunilkumarqv
Valued Contributor II

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

rajeshthakur292
Contributor II

Re: Set analysis - sum of last 30 days

Hi,

Should it not be -

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


Thanks,

RT

Ivan_Bozov
Valued Contributor

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

paola_valenti
New Contributor III

Re: Set analysis - sum of last 30 days

But it is not

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

?

helge_jorg
New Contributor III

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

helge_jorg
New Contributor III

Re: Set analysis - sum of last 30 days

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

helge_jorg
New Contributor III

Re: Set analysis - sum of last 30 days

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

helge_jorg
New Contributor III

Re: Set analysis - sum of last 30 days

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

Please see original post - I have updated it with more information

Ivan_Bozov
Valued Contributor

Re: Set analysis - sum of last 30 days

Can you share a sample app with us?

Community Browser