2 Formula in if expression

Thu, 11 Jul 2013 05:19:12 GMT

Hi, I'm so new for Qlikview and i need suggestion from here to solve my problem "Out of Memory Object".

Fact Data Size : 580M Record (Sales: 2 Year , 1 Quarter)

Product Hierachy : Division >> Group >> Department >> Class >> SubClass >> Item

QVW size = 60GB

Logic : Startup formula is Month To Date (No Selection)

When select any time series the formula will change follow selected

Formula:

=if( GetSelectedCount(Year)>0

or GetSelectedCount(Month) > 0

or GetSelectedCount(Quater) > 0

or GetSelectedCount([Day of Week]) > 0

or GetSelectedCount(Week) > 0

or GetSelectedCount(Day) > 0

or GetSelectedCount(Holiday) > 0

or GetSelectedCount(ACCOUNTING_WEEK) > 0 , $(vNetSalesTY),$(vNetSalesMTDTY))

$(vNetSalesTY) = Sum(if(Year=$(=vMaxBillingYear) and Date <=$(=(max([Billing Date]))),[Net Sales]))

$(vNetSalesMTDTY) = Sum(if(_FactYear=$(=(vMaxBillingYear)) and _FactMonth = $(=(vMaxMonthNum)) and Date <=$(=(max([Billing Date]))), [Net Sales]))

I do know this is too complex formula , i try to find out greater than thing or i should go back to use SET formular again.

Thank you very much.

My server Spec.

Re: 2 Formula in if expression

kaushiknsolanki — Thu, 11 Jul 2013 07:08:55 GMT

Hi,

Try this expressions.

For vNetSalesTY =

Sum({<Year = {"$(vMaxBillingYear)"},Date = {"<=$(=max(Billing Date))"}>}Net_Sales)

For vNetSalesMTDTY =

Sum({<FactYear = {"$(vMaxBillingYear)"},FactMonth = {"$(vMaxMonthNum)"},Date = {"<=$(=max(Billing Date))"}>}Net_Sales)

Always Remember Sum(if...) is a resource intensive expression. Dont use it when you have millions of records.

Always try to convert your expression in set expression.

Regards,

Kaushik Solanki