Set analysis / Daily Vs MTD

[#1]

I am a pretty novice user so if I am a little confused, please be nice J

I am trying to show our daily sales and our MTD (month to date) sales in the same pivot table in Qlickview.

I had made some progress using set analysis. I was forced to aggregate the set analysis to increase its flexibility.

aggr(sum({1-$} sell_price),major_group)

(Major group are just a dimension against which we report)

We select the current day which show daily sales

The set analysis then shows the excluded data (i.e. the MTD figure)

This works perfectly, however the MTD figure (which is based on the set analysis formulae) is not performing correctly.

If any particular major group has not sold in the selected day, there is totally no reference, not even the MTD figure.

I can only conclude this is because the set analysis only includes those majors which have been picked up in the daily sales.

SO my two questions are this:

Is this the case and is their any way around this?

How can I calculate/show a day and MTD total, if I can not resolve this issue?

[#2]

Hi

Try doing something like Aggr ( Sum ( { $ < major_group={"*"} > } sell_price ) , major_group )

This should ensure you get the major groups back that are not automatically included in the standard analysis.

[#3]

Hi Nigel

I'll be honest; I only understand the basics of Set analysis but when I put your code in my MTD data simply mirror my daily data.

So I thought I give more information.

Director	PM	major_group	MTD sell	MTD adj	MTD Credits	Daily sales	Daily Margin	Daily credits
Andy Dow	Adam Brind	BEL	£1.00	£1.00	£1.00	£1.00	£1.00	£1.00
Andy Dow	Adam Brind	HDD	£2.00	£2.00	£2.00	£2.00	£2.00	£2.00
Andy Dow	Adam Brind	MRY	£3.00	£3.00	£3.00	£3.00	£3.00	£3.00
Andy Dow	Adam Brind	OTD	£4.00	£4.00	£4.00	£4.00	£4.00	£4.00
Andy Dow	Ashley Watts	BTE	£5.00	£5.00	£5.00	£5.00	£5.00	£5.00

My set analysis is always based on at least three dimensions the main one being major. (PS: I have cleansed the data to reflect the low figures - *company policy*)

Perhaps your suggestion would work if you'd or anyone else be kind enough to explain the logic behind it.

Thanks

[johnw]

So is this based on the current date? Something like this:

Daily sales: sum({<Date={'$(=today())'}>} Sales)
MTD sell: sum({Date={">=$(=monthstart(today())) <=$(=today())"}>} Sales)

You may need to format today() and monthstart(today()) if your Date field uses a different format than you've set up as the default.

The attached isn't this solution. It's from the response I prepared before I saw your sample chart. I'm including it anyway in case it's helpful in some way, even though it looks like it isn't what you want. In the below response, I comment that set analysis won't work for MTD, and yet I use it in the response above. The reason it works in the response above is that you're only showing ONE date in the chart, not multiple dates. Anyway, here's my old response:

"OK, so you have a chart with the day as a dimension. Then you have sum(sell_price) as an expression for the day's sales. Now you want an expression for the month to date sales for that day, and you want to see it even when there were no sales for that day?

First, set analysis executes outside of the context of the current row of the table. It executes once for the whole table. So if you write your month to date sale expression as set analysis, it won't return expected results.

Second, the dates with no data disappearing has nothing to do with set analysis. It is a feature of QlikView that combinations of dimensions for which there are no data are simply not displayed. That's normally exactly what you want, but can become an annoyance when dealing with dates or other continuous dimensions. One common solution is to add a "date island" disconnected from your date with all dates of interest. Then you connect it to your actual dates in the expression with an IF statement. Unfortunately, the chart is then slow to execute if you have a large data set.

See the attached application for a pivot table doing what it sounds like you want. It uses these expressions:

Sales for Day: sum(if("Sales Date"="Date","Sales"))
MTD Sales: rangesum("Sales for Day",above("MTD Sales"))

If that's not what you're trying to do, and your use of aggr() and {1-$} makes me think perhaps it isn't, can you post some example data and desired results, or a sample QlikView application demonstrating the problem and desired solution?"

[#5]

Hi all.

If anyone is interested I have actually managed to do this myself

let Sy =date(num(today()),'YYYY-MM-01');
let Sy1 =date(num(today()),'YYYY-MM-DD')-1;

LOAD [RD-ID],
dim_invoice_key,
cono,
warehouse,
invoice_no,
ConsolidatedInvoice,
invoice_date,
order_no,
order_line,
item_no,
source,
customer,
customer_class,
major_group,
minor_group,
item_div,
despatch_no,
direct_del_flag,
quantity,
sell_price,
cost,
margin,
[Sales Margin],
pref_supplier,
product_group,
promo_text,
opg_deal_version,
OPG_price,
OPG_percent,
OPGDeal_FrontEnd,
opg_supplier,
opg_comment,
opg_enduser,
opg_bundleid,
M_S,
reason_code,
credit_reason,
vat_number,
customer_name,
unit_price,
unit_cost,
salesperson,
team,
overgroup,
account_owners,
promo_text2,
backend_addback,
customer_rebate,
customer_rebate_provision,
pereferred_partner,
customer_order_ref,
gl_flag,
base_cost,
list_price,
updated,
base_list_price,
frontend_addback,
region_code,
period,
prod_manager,
type,
DD_PO_NO,
DD_PO_Line,
ProductManager,
line_value_prime,
vat_value_prime,
vat_code,
vat_rate,
currency_code,
mrupdated,
date_created,
left(invoice_no,1) as Query,
if(date(invoice_date,'YYYY-MM-DD') < $(Sy1),sell_price,0) as MTD,
if(date(invoice_date,'YYYY-MM-DD') = $(Sy1),sell_price,0) as TD,
if(date(invoice_date,'YYYY-MM-DD') < $(Sy1),(margin + backend_addback + frontend_addback),0) as MTDADJ,
if(date(invoice_date,'YYYY-MM-DD') = $(Sy1),(margin + backend_addback + frontend_addback),0) as TDADJ,
if(date(invoice_date,'YYYY-MM-DD') < $(Sy1),(IF(LEFT(invoice_no,1)='D',sell_price*0.03,0)),0) as MTDHIT,
if(date(invoice_date,'YYYY-MM-DD') = $(Sy1),(IF(LEFT(invoice_no,1)='D',sell_price*0.03,0)),0) as TDHIT
FROM
[\\HC-QLIKVIEW1\qvd\DataWH\RawData_Complete.qvd]
(qvd)

wHERE invoice_date>='$(Sy)';

The 2nd let defines the last days date and the if allows me to include or exclude days based upon it.

This will not work for weekend but you could manually adjust by adding -2 or by using netdays function

[#6]

Hi,

I'm also trying to do the same... but my YEAR and MONTH are separated in 2 columns as digit

Sum ({$<YEAR={'$(=YEAR(today()))','$(=YEAR(today())-1), , MONTH={'>=$(=month(yearstart(today()))) <=$(=month(today()-1))'}} >} Sales)/1000

I wanted to see YTD & MTD for this n last year. when user select year or month, the data should not change at all.

Any help? Thanks.