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

[laxmanqlik]

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

[michael_klix]

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]

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]

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]

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]

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]

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