set analysis

[nareshthavidishetty]

Hi,

We need set analysis to get this condition in the below logic's,

Condition: [Same Store Sales Calc] ={'Y'}

Logic1: sum( aggr( rangesum( above( sum(ESTCUSTS),0,31) ),COUNTDATE))

Logic2: if(RowNo()=0,[Invoice Count]/[Adjusted Traffic],[Invoice Count MTD]/[Adjusted Traffic MTD])

[sunny_talwar]

May be this:

Sum({<[Same Store Sales Calc] ={'Y'}>} Aggr(RangeSum(Above(Sum({<[Same Store Sales Calc] ={'Y'}>}ESTCUSTS), 0, 31)), COUNTDATE))

Logic 2 are field names or expression labels?

[nareshthavidishetty]

Hi,

It's an expression lable.

Thanks..

[sunny_talwar]

Is [Invoice Count] a field or expression label for another expression in this table?

[nareshthavidishetty]

Hi,

It's an field value only..

Thanks...

[nareshthavidishetty]

Hi Sunny,

We have used the expression it self as an expression.Please find below..

Logic: if(RowNo()=0,[Invoice Count]/[Adjusted Traffic],[Invoice Count MTD]/[Adjusted Traffic MTD])

,[Invoice Count] = Sum({$<[Same Store Sales Calc] ={'Y'}>}INVCOUNT)

[Adjusted Traffic] = Sum({$<[Same Store Sales Calc] ={'Y'}>}ESTCUSTS)

[Adjusted Traffic MTD] = Sum({<[Same Store Sales Calc] ={'Y'}>} Aggr(RangeSum(Above(Sum({<[Same Store Sales Calc] ={'Y'}>}ESTCUSTS), 0, 31)), COUNTDATE))

Thanks..

[sunny_talwar]

So if this is the case, it should work now. Have you checked? Is it not working?

[nareshthavidishetty]

Yeah its working ..but for some locations it is showing null..but when i have test it individually it has data...

Below is the script..

LET datatable='Traffic03Counters';

Let vQVDFileName = vQVDPath & 'Traffic03_Counters.QVD';

$(datatable):

LOAD LOCATION,

     Market,

     ADASTX,

     AVGINVOICE,

     CLOSERATE,

     COUNTDATE,

     CUSTPAY,

     DOORID,

     ESTCUSTS,

     INVCOUNT,

     STAFFCOUNT,

     TRAFCOUNT,

     WRITSALES,

     [Traffic Adjusted Traffic Count],

     [Traffic Number of Invoices],

     [Traffic Written Sales],

     [Traffic Average Invoice Amt],

     [Traffic Closing Rate Pct]

FROM

(qvd);

left join($(datatable))

SameStore_Sales:

LOAD   Location as LOCATION,

     date(AuditDate, 'M/D/YYYY') as AuditDate,

     PostedSales,

     [Same Store Sales Calc]

FROM

[..\..\..\QVD\Retail Sales\SameStoreSales_Calculation_Daily.QVD]

(qvd);

Thanks...