Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sales Last 12 Months

Hi,

I have a formula in Pivotable, which is correctly however it need to much time for the result.

Is there any other formula to same result with few time

my formula is:

sum(all<[Calender Year Month]> if(
InMonthToDate([Calender Year Month],Vmax,0) or
InMonthToDate([Calender Year Month],Vmax,-1) or
InMonthToDate([Calender Year Month],Vmax,-2) or
InMonthToDate([Calender Year Month],Vmax,-3)or
InMonthToDate([Calender Year Month],Vmax,-4)or
InMonthToDate([Calender Year Month],Vmax,-5) or
InMonthToDate([Calender Year Month],Vmax,-6)or
InMonthToDate([Calender Year Month],Vmax,-7)or
InMonthToDate([Calender Year Month],Vmax,-8)or
InMonthToDate([Calender Year Month],Vmax,-9)or
InMonthToDate([Calender Year Month],Vmax,-10)or
InMonthToDate([Calender Year Month],Vmax,-11)
,Billing_Header.Billing_Header.Net_Value_Inv))

Vmax is variable

Vmax = Monthname(max([Calender Year Month]))

i click on 2008 JULY i become result as follows:

August 2007 100

September 2007 150

October 2007 180

November 2007 185

December 2007 129

January 2008 157

February 2008 147

March 2008 198

April 2008 247

May 2008 258

June 2008 292

July 2008 178

1 Solution

Accepted Solutions
Not applicable
Author

Hi Jsn,

Thanks for your Help.

Regards

Shideh

View solution in original post

3 Replies
jsn
Master
Master

Hi Shideh,

Set analysis is your friend for this.

Basically something like this:

sum( {1<Year = {">1978<2004"}>} Sales )

Instead of hard coding 1978 and 2004 you want to pick out the selected value, 2008 July, (or rather the underlying date value).

For the lower bound of the range you can use the date value in combination with addmonths() to subtract 12 months from the selected period thus giving you a range from 12 months back up until the selected value. There are examples of how to do a range selection with set analysis on the forums.

Not applicable
Author

Hi Jsn,

Thanks for your Help.

Regards

Shideh

View solution in original post

Not applicable
Author

Hi Jsn,

I have a formula in Pivotable for the L12 Month :

Sum({1<[Kalender.Kalender.Eindeutiger Monat]= {"$(='<='&Only([Kalender.Kalender.Eindeutiger Monat])&'>'&Only(date(addmonths([Kalender.Kalender.Eindeutiger Monat],-12),'MMM-YYYY')))"} >} Billing_Header.Billing_Header.Net_Value_Inv)

It works Ok, but when i filter for just one Customer, it show the same value as for all Customer.

i remove 1 and replace with $. it show just one Month not L12 Month. the value is just for one Month

i select in Calender 2009 March for the Sales of Last 12 Month (March-2009). i will see the April-2008 to March 2009

see blow (the formular exclude 1)

Sum({$<[Kalender.Kalender.Eindeutiger Monat]= {"$(='<='&Only([Kalender.Kalender.Eindeutiger Monat])&'>'&Only(date(addmonths([Kalender.Kalender.Eindeutiger Monat],-12),'MMM-YYYY')))"} >} Billing_Header.Billing_Header.Delivery_Charges)

March-2009 180

do you have other Idea?

Best regards,

Shideh