topic Re: set analysis in QlikView

set analysis

nareshthavidishetty — Wed, 25 Nov 2020 16:16:04 GMT

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])

Re: set analysis

sunny_talwar — Wed, 31 Aug 2016 11:08:39 GMT

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?

Re: set analysis

nareshthavidishetty — Wed, 31 Aug 2016 11:38:09 GMT

Hi,

It's an expression lable.

Thanks..

Re: set analysis

sunny_talwar — Wed, 31 Aug 2016 11:49:03 GMT

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

Re: set analysis

nareshthavidishetty — Wed, 31 Aug 2016 12:01:39 GMT

Hi,

It's an field value only..

Thanks...

Re: set analysis

nareshthavidishetty — Wed, 31 Aug 2016 12:09:24 GMT

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..

Re: set analysis

sunny_talwar — Wed, 31 Aug 2016 12:14:04 GMT

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

Re: set analysis

nareshthavidishetty — Wed, 31 Aug 2016 12:22:44 GMT

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...