Before Function in Pivot table

[gulshan_rohilla]

Hello everyone,

I have a pivot table in Qlik Sense in which two measures were there, first is Sales and Second one is upto Sales.

1.) Sales :- Sales is given(already calculated)

2.) Upto Sales :- Derived measure from sales like if i have sales from Jan to Dec then in upto sales measure i want in jan month jan sales shows, in feb = jan+feb, in mar=jan+feb+mar, in apr=jan+feb+mar+apr.......................and in dec=jan+feb+mar+apr+..................+dec (i.e. upto that month)

I have written some expression but i am facing one issue i.e.

Jan = Jan

Feb = Jan + Feb

Mar = Feb +Mar

...

...

...

...

Dec = Nov + Dec

i.e. only previous month plus current month.

below is my attached expression:-

IF($(eSalesPartyWiseYTD)<>NULL() AND BEFORE($(eSalesPartyWiseYTD))<>NULL(),RANGESUM($(eSalesPartyWiseYTD),BEFORE($(eSalesPartyWiseYTD))),

IF((ISNULL(BEFORE($(eSalesPartyWiseYTD))) OR BEFORE($(eSalesPartyWiseYTD))='-') AND $(eSalesPartyWiseYTD)<>NULL(),$(eSalesPartyWiseYTD),

      IF(ISNULL($(eSalesPartyWiseYTD)) AND BEFORE($(eSalesPartyWiseYTD))<>NULL(),BEFORE($(eSalesPartyWiseYTD)),

          IF(ISNULL($(eSalesPartyWiseYTD)) AND ISNULL(BEFORE($(eSalesPartyWiseYTD))),'0'))))

Below is the attached screenshot.

Kindly help me

[marcus_sommer · Accepted solution]

Try it with:

RANGESUM(BEFORE($(eSalesPartyWiseYTD), 0, columno(total)))

- Marcus

[gulshan_rohilla]

Hi Marcus,

Thank you so much for your reply.

it works good.

Can you explain me the above expression.

[marcus_sommer]

Before() belonged to the interrecord-functions and could not just return a single value else by using the second and third parameter of the function you could specify an offset from where the return should be starting and how many rows/columns respectively "calculated cells" should be returned (and because it could be more than one return-value the rangesum() is needed to handle these n return-values).

- Marcus

[gulshan_rohilla]

Thanks Marcus

Now i got all the things