topic Re: How to change Straight table Expression in to Script logic by getting same result ? in QlikView

How to change Straight table Expression in to Script logic by getting same result ?

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have a Straight table like Below . And i have got the average like Below

The Business Formula to Calculate Average:

(Today-Previous day)/Previous day * 100

The Expression to Calculate Average:

=Num((Sum(TGB_Counts)/Above(Sum({<Date>}TGB_Counts)) - 1) * Avg(1),'#,##0.00')

(The Expression is based on Above Business Formula)

Note: Now iam getting Correct Average,But My Business Sudden Request is to bring the above Average Expression logic in Script

Is there any way to get the above expression logic in Script So that i Dont Want to Perform Expression to Get Average. I can easily add Average as Dimension in Table Box Instead of Straight table

I Need above expression as Script Logic so that i will get Average as a field and i can add it as Dimension in Script.

Note: The Script logic output must be same like Above Straight table expression average values.

I am Searching a solution for it. kindly Help me out of this.

Please Find the attached Qvw and Excel Data for your Referance

Thanks,

Muthukumar

Re: How to change Straight table Expression in to Script logic by getting same result ?

sunny_talwar — Wed, 21 Jun 2017 17:08:34 GMT

Try this

Sheet1:

LOAD [TGB Name],

Date as Date_Text,

Left(Date, 10) as Date,

TGB_Counts

FROM

[Pos.xlsx]

(ooxml, embedded labels, table is Sheet1);

FinalTable:

LOAD *,

Num(If([TGB Name] = Previous([TGB Name]), TGB_Counts/Previous(TGB_Counts) - 1), '#.00') as Average

Resident Sheet1

Order By [TGB Name], Date;

DROP Table Sheet1;

Re: How to change Straight table Expression in to Script logic by getting same result ?

Wed, 21 Jun 2017 22:41:54 GMT

Thank you So much Sunny . May i Know how the above logic Exactly work.can you Show me with Sample Data Set

Because i dont Know how it Exactly Works. i can Under Stand up to below Step

Num(If([TGB Name] = Previous([TGB Name]), TGB_Counts/Previous(TGB_Counts)

i cant understand the -1 part and How it gets with the below Formula

The Business Formula to Calculate Average:

(Today-Previous day)/Previous day * 100

Can You Please Explain Me the Logic Behind it So that it will be help full for my Learning process

Thanks,

Muthukumar

Re: How to change Straight table Expression in to Script logic by getting same result ?

sunny_talwar — Thu, 22 Jun 2017 11:13:11 GMT

May i Know how the above logic Exactly work.can you Show me with Sample Data Set

I did attach the sample you attached in my response... did you get to check it out? I mean the logic is simple

I am doing this

TGB_Counts/Previous(TGB_Counts) - 1

which is saying

Divide the current row of TGB_Counts with the previous row of TGB_Counts and subtract 1 from it. Just like you did this on the front end.....

Does that make sense?