Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to separate the current day data from last three days data?

Hi all,

I have two Straight table like below

one for Positive  values and another one is for negative values

Positive Straight table :

NameDateValueAvg.Value.
A1/1/2017PM40
B1/2/2017PM70.75

Negative Straight table:

NameDateValueAvg.Value.
C1/1/2017PM4-0.42
D1/2/2017PM3

-0.25

The Expression I am currently using is :for Positive

=if(Num((Sum(values)-above(TOTAL sum( values ) ))/above(TOTAL sum( values ) ),'#,##0.00')>0,

Num((Sum(values)-above(TOTAL sum( values ) ))/above(TOTAL sum( values ) ),'#,##0.00'))

The Expression I am currently using is :for Negative

=if(Num((Sum(values)-above(TOTAL sum( values ) ))/above(TOTAL sum( values ) ),'#,##0.00')<0,

Num((Sum(values)-above(TOTAL sum( values ) ))/above(TOTAL sum( values ) ),'#,##0.00'))

I want output like below tables: I want to get output for a Current date that is 1/2/2017 and previous date like 1/1/2017 separately.

Positive Straight table for 1/1/2017:

NameDateValueAvg.Value.
A1/1/2017PM40

Positive Straight table for 1/2/2017:

NameDateValueAvg.Value.
B1/2/2017PM70.75

Negative Straight table for 1/1/2017:

NameDateValueAvg.Value.
C1/1/2017PM4-0.42

Negative Straight table for 1/2/2017:

NameDateValueAvg.Value.
D1/2/2017PM3

-0.25

kindly help me out of this.

Thanks,

Muthu

4 Replies
sathishbabu_s_g
Contributor III
Contributor III

Hi,

Can you give some inputs how the actual data looks like ? I think Straight table which you refer to is Qlikview object.

Is it has following columns such as Name, Date and Value

ahaahaaha
Partner - Master
Partner - Master

Hi Muthu,

Maybe like this?

Positive Straight table for 1/1/2017, for example:

Num((Sum({<values={">=0"}, Date={'1/1 / 2017PM'}>}values)-above(TOTAL sum( values ) ))/above(TOTAL sum( values ) ),'#,##0.00')


To include a value of zero in the table, we changed the condition for [value] ">" to "> =". On the chart itself, to show zero values, you can add expression Sum({<values={">=0"}, Date={'1/1 / 2017PM'}>}1) and subsequently hide it (see the example in the attached file).

Similarly, we repeat everything for other dates. Need change only the dates and conditions for [value].

Regards,

Andrey

Not applicable
Author

Thanks for your response Andrey.

the average values are not given statically as an input it is calculated based on the value column in the table by using below expression

The Expression I am currently using is :for Positive

=if(Num((Sum(values)-above(TOTAL sum( values ) ))/above(TOTAL sum( values ) ),'#,##0.00')>0,

Num((Sum(values)-above(TOTAL sum( values ) ))/above(TOTAL sum( values ) ),'#,##0.00'))

The Expression I am currently using is :for Negative

=if(Num((Sum(values)-above(TOTAL sum( values ) ))/above(TOTAL sum( values ) ),'#,##0.00')<0,

Num((Sum(values)-above(TOTAL sum( values ) ))/above(TOTAL sum( values ) ),'#,##0.00'))

I above expression is formed based on below formula:

Formula:

(Today value -Previously day value)/Previous day value

Functionality:

Name B:

Today=1/2/2017                value:7

Previous day=1/1/2017      value:  4

Formula:

(7-4)/4=0.75


NameD:

Today=1/2/2017                value:3

Previous day=1/1/2017      value:4

Formula:

(3-4)/4=-0.25

sathishbabu.s.g

Same like I want average values for two days data in two straight tables.

Kindly Help me out of this

Thanks,

Muthu

ahaahaaha
Partner - Master
Partner - Master

Hi Muthu,


May be like in attached file (if I understood you correctly)


Regards,

Andrey