Last 12 Months Sales

[Anonymous]

Hi Guys,

Im new on Qlikview and trying to get last 12 months sales by Set Analysis, but Im having an issue its not returning any value. Could you please help me out.

Farrukh

=Sum({$<Date={">=$(=Date(MonthStart(AddMonths(Max(Date),-11)),'YYYYMMDD')<=$(=Date(MonthStart(AddMonths(Max(Date),0)),'YYYYMMDD'))"}>} Distinct Total_Sales)

[sunny_talwar]

I think you are missing a parenthesis:

=Sum({$<Date={">=$(=Date(MonthStart(AddMonths(Max(Date),-11)),'YYYYMMDD'))<=$(=Date(MonthStart(AddMonths(Max(Date),0)),'YYYYMMDD'))"}>} Distinct Total_Sales)

[johnw]

I'm confused about the use of distinct. If two records happen to have the same sales, I'd think you'd want them both. If you have any other calendar-like fields - Year, Month, that sort of thing, you'd need to exclude them. Anyway, I'd write it something like this, probably:

sum({<Date={"=Date>=monthstart(max(Date),-11)) and Date<monthstart(max(Date),1)"},Year,Month>} Total_Sales)

[Anonymous]

sum({<Date={'>=$(=Max(Date))-11)<=$(=Max(Date))'}>}Sales)

[Anonymous]

Hi Sunny,

I put the parenthesis as you mentioned but its only showing the current selected month sale. not for last 12 months.

The DATE I have in 20161110 which is 'YYYYMMDD' that's why I am formatting the date after Addmonths.

But its still not showing the result

Farrukh

[Anonymous]

Hi John,

its returning zeros. I that's why I used the format 'YYYYMMDD' because date I have it in this format.

Farrukh

[sunny_talwar]

Then as John mentioned, try to ignore selection in other date fields where you might be making selections

=Sum({$<Date={">=$(=Date(MonthStart(AddMonths(Max(Date),-11)),'YYYYMMDD'))<=$(=Date(MonthStart(AddMonths(Max(Date),0)),'YYYYMMDD'))"}, Month, MonthYear, Year, Quarter, QuarterYear, Week, WeekYear>} Distinct Total_Sales)

Also, I agree with John that use of DISTINCT seems to be in correct unless you are very confident that no two total_sales number are going to ever match. But why take that risk, why not just use the expression without DISTINCT

=Sum({$<Date={">=$(=Date(MonthStart(AddMonths(Max(Date),-11)),'YYYYMMDD'))<=$(=Date(MonthStart(AddMonths(Max(Date),0)),'YYYYMMDD'))"}, Month, MonthYear, Year, Quarter, QuarterYear, Week, WeekYear>} Total_Sales)

[sunny_talwar]

That is the benefit of using search string that you don't have to worry about formats, as long as they are read as dates, you can use it like this. I think John's expression might have left some extra parenthesis in there

Sum({<Date={"=Date>=monthstart(max(Date),-11)) and Date<monthstart(max(Date),1)"},Year,Month>} Total_Sales)

Try this:

Sum({<Date={"=Date>=monthstart(max(Date),-11) and Date<monthstart(max(Date),1)"}, Year, Month>} Total_Sales)

[Anonymous]

Thanks sunny,

I use the above suggested examples but I really do not know why its not working. I'm sure that I'm still missing something.

Now I tried instead of date a [Year Month], but still I'm having the same result

=Sum({$<[Year Month]={">=$(=Date(MonthStart(AddMonths(Min(Date),-4)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(Date),0)),'YYYYMM'))"}>} Total_Sale)

Could you please advise me related to above?

Kind regards,

Farrukh

[Anonymous]

Thanks a lot Sunny,

It has been sorted  by including the Year=, Month= as you previously mentioned.

=Sum({$<Year=, Month=, [Year Month]={">=$(=Date(MonthStart(AddMonths(Min(Date),-11)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(Date),0)),'YYYYMM'))"}>} F_TOTAL_INFLOW_M3_DAY)