Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

Screenshot_1.png

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

1 Solution

Accepted Solutions

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;

View solution in original post

3 Replies

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;

View solution in original post

Not applicable

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

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?