Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
laxmanqlik
Not applicable

How to get the sum of last 15 records in a KPI of a month

Hi Friends,

my client requirement is "whenever he made a selection in month field, the KPI app object has to show the last 15 days sales summary"

is there any way to way to get it

Thank you

6 Replies
michael_klix
Not applicable

Re: How to get the sum of last 15 records in a KPI of a month

Hi,

sounds interesting but can you please specify your problem?

I understand your customer selects a month.... means a month and year or just a month? And how shall the "last 15 days" be understood? 15.-30st of the month?

Last 15 days back from any given day would be something I understand but that sounds strange to me.

Keep Qliking

Michael

laxmanqlik
Not applicable

Re: How to get the sum of last 15 records in a KPI of a month

Hi Michael,

my customer selects month and year.

last 15 days of the selected month refers last day of the month-15 days.

we should get the sales of last 15 days in KPI

michael_klix
Not applicable

Re: How to get the sum of last 15 records in a KPI of a month

sounds like a typical set analysis:

once month and year are seleted, by max(yourDATEfield) you should get the last available day of the month.

Then in set analysis do something like

avg({<DATE={">date(max(DATE}-15)">}sales)

The date function in the set analysis ensures that the formatting of the "-15" result is a date, not a number.

Not sure if there might occur an issue if the last day of the month is >15th so the resilt of "-15" goes back to the last month. But actually the month selection should ensure that in that case only the values of the choosen month are averaged.

Keep Qliking

Michael

parevrac
Not applicable

Re: How to get the sum of last 15 records in a KPI of a month

Try creating a flag field in the data load script

if(num(YourDateField) >= (today()-15), 1, 0) as _15DayFlag

this flag can then be use in set analysis

sunny_talwar
Not applicable

Re: How to get the sum of last 15 records in a KPI of a month

I think you are missing a curly bracket and dollar sign expansion. May be this:

Avg({<DateField = {"$(='>=' & Date(Max(DateField)-15, 'DateFieldFormatHere'))"}>}sales)

Remember to add the DateFieldFormat within the single quotes.

Best,

Sunny

prat1507
Not applicable

Re: How to get the sum of last 15 records in a KPI of a month

Hi laxman

Try to store the Selections in a variable, for ex. v_sel=GetFieldSelections(Dim_Month)&' '&GetFieldSelections(Dim_Year). Let's say if he selects Apr & 2016 respectively.

The variable would store value 'Apr 2016'

Now, for getting the sum of Sales for last 15 days for the month of April 2016, you can use the following set analysis:

SUM({<Dim_Date={'=Date(MonthsEnd(1,Date#('$(v_sel)','MMM YYYY'))-15)'}>}Sales)

You can modify the date format based on your selections.

Thanks

Pratyush