topic Re: Front End Logic Calculation in QlikView

Front End Logic Calculation

Anonymous — Sat, 27 Sep 2014 05:36:18 GMT

Dear All,

Need a small help to build a logic at front end.

I have attached a simple test file(attached) which is I am getting in my report but I have to achieve the result of target excel(attached).

Flow:

I am getting a dynamic value through a variable say VDyanmicvariable=50 which is Initial OutStanding, now I have required fields to calculate Closing OutStanding.

and for next month, my initial outstanding will be closing outstanding of previous month.

Please suggest, how I can calculate at front end???

Regards,

Balraj

Re: Front End Logic Calculation

jyothish8807 — Sat, 27 Sep 2014 05:47:47 GMT

Hi balraj,

Do you have any formula to calculate closing out standing?

Regards

Re: Front End Logic Calculation

Anonymous — Sat, 27 Sep 2014 05:50:12 GMT

Yes KC,

It will be equal to:

Initial O/S+Sales-Collection-Claims= closing O/s

It is mentioned in attached excel.

Re: Front End Logic Calculation

Anonymous — Sat, 27 Sep 2014 07:30:32 GMT

Hello Experts,

Any update on above shared logic???

Re: Front End Logic Calculation

MarcoWedel — Sat, 27 Sep 2014 09:13:46 GMT

Hi,

one solution might be:

hope this helps

regards

Marco

Re: Front End Logic Calculation

jyothish8807 — Sat, 27 Sep 2014 09:42:43 GMT

Awesome, this will help Balraj Ahlawat for sure. I tried the same using the below, but it didn't work.Can you tell me why?

=if(Date=var1,Var,Above(closing outstd))

var1=min(Date)

Var=50

Regards

Re: Front End Logic Calculation

Anonymous — Sat, 27 Sep 2014 09:48:48 GMT

Thanks Marco,

Really Helpful.

after getting this one more calculation I need to do and need your help to achieve that:

Now I have to add a column to calculate days in the same table:

with following logic:

=IF(Closing Outstanding-Sales<0, Closing O/S/Sales*30,

IF(Closing OutStanding-Sales-Previous(Sales)<0, ((Closing OutStanding-Sale)/Previous(Sales)*30)+30,

IF(Closing OutStanding-Sales-Previous(Sales)-Previous(Previous(Sales)<0, ((Closing OutStanding-Sale-Previous(Sales))/Previous(Previous(Sales))*30)+60, "<90 Days"))))

Is this calculation possible at Front End where we can replace Previous() with some other front end function as Previous is script level function???

Pls Suggest.

Re: Front End Logic Calculation

Anonymous — Sat, 27 Sep 2014 09:49:28 GMT

Yes,

It's really helpful.

Re: Front End Logic Calculation

MarcoWedel — Sat, 27 Sep 2014 09:53:26 GMT

please close this thread if your initial question is answered and open another thread for this new problem.

thanks

regards

Marco

Re: Front End Logic Calculation

Anonymous — Sat, 27 Sep 2014 09:58:01 GMT

Done Marco,

But need help on this:

Now I have to add a column to calculate days in the same table:

with following logic:

=IF(Closing Outstanding-Sales<0, Closing O/S/Sales*30,

IF(Closing OutStanding-Sales-Previous(Sales)<0, ((Closing OutStanding-Sale)/Previous(Sales)*30)+30,

IF(Closing OutStanding-Sales-Previous(Sales)-Previous(Previous(Sales)<0, ((Closing OutStanding-Sale-Previous(Sales))/Previous(Previous(Sales))*30)+60, "<90 Days"))))

Is this calculation possible at Front End where we can replace Previous() with some other front end function as Previous is script level function???

Please suggest as you have understood it very well.

Appreciate your efforts.

Re: Re: Front End Logic Calculation

MarcoWedel — Sat, 27 Sep 2014 09:59:16 GMT

Hi,

maybe

var1=min(total Date)

and

if(Date=$(var1) ...

could work.

min(Date) in an expression when using Date as dimension should deliver just the same Date value per Date/Dimension value.

hope this helps

regards

Marco

Re: Re: Front End Logic Calculation

Anonymous — Sat, 27 Sep 2014 10:07:19 GMT

Say,

If user select a Calender 2014, Apr the my makedate() function will make it 1st April 2014 and in variable value will be 50 as per last thread.

How min(Date) which will return 1st April 2014 only would work similar to previous to previous functionalities??

Re: Re: Front End Logic Calculation

Anonymous — Sat, 27 Sep 2014 10:12:54 GMT

Here each time I need current and previous values of sales till my Closing Outstanding becomes<0.

I have done this at back end but now things are dynamic, I have to calculate them at front end only

I need a function which would work similar to previous() at front end.

Pls Suggest.

Re: Front End Logic Calculation

jyothish8807 — Sat, 27 Sep 2014 10:16:12 GMT

Tried this also,but didnt work

Regards

Re: Front End Logic Calculation

MarcoWedel — Sat, 27 Sep 2014 10:16:31 GMT

Hi,

I'm not quite sure about what you like to achieve with this expression, but tried for a solution.

Is this what you are looking for:

regards

Marco

Re: Front End Logic Calculation

Anonymous — Sat, 27 Sep 2014 10:43:45 GMT

It is required for Daily Sales Outstanding.

and it should be like this:

Date	Initial O/S	Sales	Collection	Claims	Closing O/S	Days
Apr-14	50	20	10	5	55	<120
May-14	55	15	5	10	55	<120
Jun-14	55	10	10	5	50	<120
Jul-14	50	15	10	5	50	105
Aug-14	50	10	8	10	42	104
Sep-14	42	10	10	10	32	84

Closing Outstanding would be compared with current sales and previous sales.

We can write one more if statement to make it <120 days.

Re: Front End Logic Calculation

Anonymous — Mon, 29 Sep 2014 05:40:11 GMT

Hello Marco,

It is required for Daily Sales Outstanding.

and it should be like this:

Date	Initial O/S	Sales	Collection	Claims	Closing O/S	Days
Apr-14	50	20	10	5	55	<120
May-14	55	15	5	10	55	<120
Jun-14	55	10	10	5	50	<120
Jul-14	50	15	10	5	50	105
Aug-14	50	10	8	10	42	104
Sep-14	42	10	10	10	32	84

Closing Outstanding would be compared with current sales and previous sales.

We can write one more if statement to make it <120 days.

Any update on this???

Re: Front End Logic Calculation

Anonymous — Mon, 29 Sep 2014 07:55:32 GMT

Marco,

Yes, I am looking for similar kind of column to add...

Could you help me to get this expression?

Regards,

Balraj

Re: Front End Logic Calculation

Anonymous — Tue, 30 Sep 2014 09:02:41 GMT

KC,

Have you got any success to get this field NewColumn say Days?

Marco had done this but not getting any response from him..

Regards,

Balraj