topic Re: How to separate the current day data from last three days data? in QlikView

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

Wed, 25 Nov 2020 16:16:04 GMT

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

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

sathishbabu_s_g — Tue, 30 May 2017 09:39:51 GMT

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

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

ahaahaaha — Tue, 30 May 2017 12:53:21 GMT

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

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

Wed, 31 May 2017 00:06:26 GMT

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

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

ahaahaaha — Wed, 31 May 2017 15:05:30 GMT

Hi Muthu,

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

Regards,

Andrey