Sum of record for max date near selected date

[paolojolly]

Hi,

I need help in qliview document

In this document I have a reference date field.

When I change the value of this date field a pivot table must be updated
For Example: if I select 01/10/2016 as date, in pivot i need to show for each customer, for max date near the selected date the
count of record in which field W_PRZ_PROMO is <= W_PRZ_PUBBL

thanks a lot to everyone

[sunny_talwar]

Would you be able to share a qvw to play around with this?

Update: I see you attached the sample, would you be able to provide some details about your expected output?

[paolojolly]

I need to have a pivot like this

CUSTOMER	MAX RIL DATE < SELECTED DATE	COUNT OF RECORD FOR MAX RIL DATE  IN WITCH W_PRZ_PROMO is <= W_PRZ_PUBBL
C0000004-001	20/09/2016	12
C0000004-002	20/09/2016	60
C0000005-009	15/09/2016	31
C0000005-012	26/09/2016	41
C0000005-013	26/09/2016	20
C0000009-001	26/09/2016	10
C0000009-001	28/09/2016	35
C0000053-001	28/09/2016	53
C0000102-001	20/09/2016	60
C0000381-001	19/09/2016	84
C0000381-002	21/09/2016	83
C0000381-003	20/09/2016	85
C0000381-004	26/09/2016	78
C0000381-006	26/09/2016	85
C0000381-007	30/09/2016	85
C0000428-001	28/09/2016	45
C0000428-005	30/09/2016	15
C0000428-006	30/09/2016	4
C0000443-009	28/09/2016	54
C0001524-001	21/09/2016	20

[sunny_talwar]

I am not sure I understand how you are coming with these numbers? Can you may be pick one customer and explain how you got 12 for may be C0000004-001

[rupamjyotidas]

Probably you need to use Aggr() to group the equation with W_PRZ_PUBBL or W_PRZ_PROMO.

Aggr(Sum(if(W_PRZ_PROMO<=W_PRZ_PUBBL,1)),C_NEGOZIO)

[paolojolly]

Se for example file DATA2.xls.

For C0000004-001 I have two values in DATA_RILEVAZIONE: 20.09.2016 and 10.03.2016.

If in the reference date field I select 4/10/2016 in the pivot table I should consider records with DATA_RILEVAZIONE 03.10.2016 (the largest <= 04.10.2016)

and for these records i have to count how many W_PRZ_PROMO is <= W_PRZ_PUBBL. In this case 72

I tried to use in the pivot

sum(IF((DATA_RILEVAZIONE) =
  (
  FirstSortedValue(distinct (DATA_RILEVAZIONE
), -(
$(v_GroupRil)
))
)
  AND W_PRZ_PROMO <= W_PRZ_PUBBL, 1))

where v_GroupRil    is

if(
  makedate(
year(DATA_RILEVAZIONE),
Month(DATA_RILEVAZIONE),
day(DATA_RILEVAZIONE)
  )
  <=
makedate(
Year($(data_rif)),
Month($(data_rif)),
day($(data_rif))
)
,
  DATA_RILEVAZIONE
)

but not function.

thanks for the support

[sunny_talwar · Accepted solution]

Try this out.

Script:

LOAD ANNO_RILEVAZIONE,

    C_AGENTE,

    AutoNumber(C_NEGOZIO&DATA_RILEVAZIONE) as Key2,

    C_ARTICOLO,

    C_CLIENTE,

    C_NEGOZIO,

    C_PROGR_NEGOZIO,

    C_SCHEDA_LIS_PUBBL,

    C_TIPO_RIL,

    DATA_RILEVAZIONE,

    W_NR_FACING,

    W_PRZ_PROMO,

    W_PRZ_PUBBL

FROM

[DATA.xls]

(biff, embedded labels, table is Sheet1$);

Straight Table

Dimension

C_NEGOZIO

Expression

FirstSortedValue(DISTINCT DATA_RILEVAZIONE, -$(v_GroupRil))

Count({<Key2 = {"=Aggr(NODISTINCT FirstSortedValue(DISTINCT DATA_RILEVAZIONE, -$(v_GroupRil)), C_NEGOZIO) = DATA_RILEVAZIONE"}>}C_NEGOZIO)

[paolojolly]

Thanks so much.

It is what I need

[sunny_talwar]

Awesome

I am glad I was able to help.

Best,

Sunny