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

[#1]

Hi all,

I have two Straight table like below

one for Positive  values and another one is for negative values

Positive Straight table :

Name	Date	Value	Avg.Value.
A	1/1/2017PM	4	0
B	1/2/2017PM	7	0.75

Negative Straight table:

Name	Date	Value	Avg.Value.
C	1/1/2017PM	4	-0.42
D	1/2/2017PM	3	-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:

Name	Date	Value	Avg.Value.
A	1/1/2017PM	4	0

Positive Straight table for 1/2/2017:

Name	Date	Value	Avg.Value.
B	1/2/2017PM	7	0.75

Negative Straight table for 1/1/2017:

Name	Date	Value	Avg.Value.
C	1/1/2017PM	4	-0.42

Negative Straight table for 1/2/2017:

Name	Date	Value	Avg.Value.
D	1/2/2017PM	3	-0.25

kindly help me out of this.

Thanks,

Muthu

[sathishbabu_s_g]

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]

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

[#4]

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]

Hi Muthu,

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

Regards,

Andrey