QVD Total

[Anonymous]

Hello All

Please help me in the below query.. Is it possible to calculate the running total after loading the data in qvd not in visualization.

I need a running total on the basis of ALID.. which can be filter on CA, MA & DA.

Please help me. I need an urgent help!!

Thanks

[#2]

Hey there,

You can do something like this:

Test:
LOAD Distinct PGLD, MAN,
     ALID
FROM
AT.xlsx
(ooxml, embedded labels, table is Sheet1);

Final:
Load PGLD,
  MAN,
     ALID,
ALID + Alt(Peek(RunningALID),0) as RunningALID
Resident Test
Order by PGLD,MAN ASC;

Drop TAble Test;

Basically you can use Peek function for running totals

[Anonymous]

Thanks a lot sir!!

I have few queries.. Sorry about that as I am not good enough in Sense till now .

What I understand is load the data and put the data in resident table and calculate period in resident table.

Sir you are using too many functions to calculate the running total. But if I am using the table where I am calculating the running total there should be only one function... Sir which function I need to use..

One more query sir.. I want the filters for CLN, MAN and on single month or multiple months.. Is it possible??

Currently I am calculating the running total in visualization so when I am selecting any particular month.. The running total not work.. Please help

[Anonymous]

Thanks Ajay sir.

Please help me to know ALT function.. Sir can I filter on month, year , quarter, CLN , DIN and MAN.. Using above logic

Thanks

[#5]

Sure.

* Alt function is used to pick the first non-null value. The reason I used it is because while summing the first row peek will look for a row above that and wont find any hence will replace null with zero in that case(so it will be used only once)

* When you filter if you want the Running Total to be re-calculated based on your filter then its gonna be impossible to cover all different combinations. Doing it on the UI is the easiest. May I ask why you would wanna do it in the script.

[Anonymous]

Thanks a lot sir.. Sir in my current visualisation I am calculating the running total in visualization.

I am using 3 columns

1. Month

2. Alid

3. Running Slid

I can easily filter on CLN, DIN, MAN.. But when I am selecting any particular month it is not calculating the running total. This might because by selecting particular month it is not considering the previous month values... And as per requirement I need to show via month quarter or year..

This is the reason I end up calculating it in script so that I can filter up on month year or quarter..

Please let me know if it make no sense to you..

[#7]

Hey Deepanshu,

You could just create a calendar with Months,Quartes and Years.

You could also use QTD and YTD.

If that's the case you can just create these flags in the backend and use these as filters in the Front-end instead of selecting individual months.

If(PGLD>= QuarterStart(today()),1,0) as QTD,

If(PGLD>= YearStart(today()),1,0) as YTD,

Ceil(Month(PGLD)/3) as Quarter,

Date(YearStart(PGLD),'YYYY') as Year,

Is my understanding correct.

Thanks