topic Re: Set Analysis : Subtract yesterday and previous day field data in QlikView

Set Analysis : Subtract yesterday and previous day field data

jenmclean — Tue, 29 Aug 2017 21:14:50 GMT

I have a data set that is only Monday-Friday data that is collected from three sources.

One source has Change data but the other two do not (difference in price from previous posting, i.e. 0.0650).

I need to create an expression that will post the Change data for the one source that currently contains data, and then take the PostedPrice from yesterday minus the PostedPrice from the day before that.

For Example: Monday would show Friday Data, Tuesday will show Monday data, etc.

I'm stumped and/or overthinking it. Any thoughts or ideas?

Yellow has Change data, Blue and Orange do not.

Data Field Names:

Term,

Date,

PostedPrice,

FuelName,

FuelType,

Product,

Change,

Discount,

Tax

Re: Set Analysis : Subtract yesterday and previous day field data

Anil_Babu_Samineni — Wed, 30 Aug 2017 01:28:40 GMT

I think you could need qualify statement for that table and set analysis for logic where you required which is

Above(Sum(sales),1)-above(sum(sales),2)

Re: Set Analysis : Subtract yesterday and previous day field data

vishsaggi — Wed, 30 Aug 2017 01:37:17 GMT

Can you share some sample data with expected output rather than an image. It would save time to work on sample data.

One way to restrict weekends is to using where clause just run the below query in your qvw file for a quick check.

For i = 1 to 20

Table1:

LOAD Date(Today() - $(i)) AS PrevDates,

WeekDay(Date(Today() - $(i))) AS PrevDay,

Date(Today() - $(i)) & '-' & WeeKDay(Date(Today() - $(i))) AS DateWeekName

AutoGenerate $(i);

Next i;

NoConcatenate

Final:

LOAD *

Resident Table1

WHERE NOT Match(PrevDay, 'Sat', 'Sun');

Drop Table Table1;