topic Re: Set Analysis for selecting previous year's December data as base value for all the months? in QlikView

Set Analysis for selecting previous year's December data as base value for all the months?

bhaveshp90 — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I want to select December month data of previous year as the baseline value for all the months for As Sold Dec (Updated OEC) as shown in the below image

But this expression is not working as shown in the image below (Snapshot_Date format like 2018-08-31, 2018-09-30, etc)

=Sum({<Month={"$(=max(Snapshot_Date)'}Snapshot_Date(MonthEnd(Max(Snapshot_Date), -1),'MMM-YY'))"}>}As_Sold)

What wrong am I doing here? Any help is greatly appreciated

thanks

Bhavesh

Re: Set Analysis for selecting previous year's December data as base value for all the months?

neelamsaroha157 — Wed, 24 Oct 2018 15:14:52 GMT

would you be able to provide a sample file?

Re: Set Analysis for selecting previous year's December data as base value for all the months?

MK9885 — Wed, 24 Oct 2018 15:48:10 GMT

Not sure if this would help , Maybe try

Sum({<Snapshot_Date = {"$(='>=' & Date(AddMonths(Max(Snapshot_Date), -1), 'YYYY-MM-DD')

& '<=' & Date(Max(Snapshot_Date), 'YYYY-MM-DD'))"}, Month, Num_Month, Year, Quarter, Week

>}As_Sold)

or shorter version can be

Sum({<Num_Month = { '$(=Max(Num_Month) )'},Month, Year = { $(=Max(Year) )} >}As_Sold)

Num_Month field is numerics of months, 1, 2, 3...12.

I think above expression would give you Max of Month of selected Year..

If you just select Year=2017, it would shoe Dec-2017

If you want previous year Dec-16 when you select 2017

Sum({<Num_Month = { '$(=Max(Num_Month) )'},Month, Year = { $(=Max(Year) -1 )} >}As_Sold)

Re: Set Analysis for selecting previous year's December data as base value for all the months?

bhaveshp90 — Thu, 25 Oct 2018 17:56:31 GMT

Please find the sample qvw in the attachments and the output (Baseline - Updated Sales) I am trying to get is shown below;

The Baseline Updated Sales for all the dates must be equal to the December month Updated Sales of the previous year.

(As shown above the Baseline - Updated Sales for all months in 2018 is equal to 2017 December month value = 1605652)

For 2018 December Baseline - Updated Sales value should be equal to 2018 December Updated Sales Value

thanks

Re: Set Analysis for selecting previous year's December data as base value for all the months?

bhaveshp90 — Thu, 25 Oct 2018 17:58:05 GMT

Please reference the below comment

thanks

Re: Set Analysis for selecting previous year's December data as base value for all the months?

neelamsaroha157 — Thu, 25 Oct 2018 19:53:07 GMT

Here you go -

Aggr(NODISTINCT Sum({<Year = {$(vPreYear)} , Month = {$(vPreYearLastMonth)}, [Cost Area] = {'Mechanical Engineering'}>}Updated_Sales) , [Cost Area])

Re: Set Analysis for selecting previous year's December data as base value for all the months?

bhaveshp90 — Tue, 30 Oct 2018 12:47:40 GMT

thank you Neelam. But if we select 2018 the expression is null which is incorrect.

Re: Set Analysis for selecting previous year's December data as base value for all the months?

neelamsaroha157 — Tue, 30 Oct 2018 12:57:06 GMT

It doesn't get on my side. Are you selecting the date or the year field?

Re: Set Analysis for selecting previous year's December data as base value for all the months?

bhaveshp90 — Tue, 30 Oct 2018 13:05:17 GMT

I'm selecting the Date field. For April '2018-04-30' the expression is null also for 2018-02-28, 2018-01-31, 2018-03-31

Re: Set Analysis for selecting previous year's December data as base value for all the months?

neelamsaroha157 — Tue, 30 Oct 2018 13:17:00 GMT

If you want to select on date, then modify your expression as -

Aggr(NODISTINCT Sum({<Year = {$(vPreYear)} , Month = {$(vPreYearLastMonth)}, [Cost Area] = {'Mechanical Engineering'}, Date = >}Updated_Sales) , [Cost Area])

Re: Set Analysis for selecting previous year's December data as base value for all the months?

bhaveshp90 — Tue, 30 Oct 2018 15:05:34 GMT

thank you neelamsaroha1575‌. This expression is working for this table So I am closing this thread. Can you please look into this Expression for getting December month value for previous Year?