Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try it with:
RANGESUM(BEFORE($(eSalesPartyWiseYTD), 0, columno(total)))
- Marcus
Try it with:
RANGESUM(BEFORE($(eSalesPartyWiseYTD), 0, columno(total)))
- Marcus
Hi Marcus,
Thank you so much for your reply.
it works good.
Can you explain me the above expression.
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
Thanks Marcus
Now i got all the things