topic Re: Select moore months in Set Analys in QlikView

Select moore months in Set Analys

Wed, 25 Nov 2020 16:16:04 GMT

Hello,

is it possible to get this?

If I select a year and lets say febr and mars and get the result for the same months in a set analysis like this?

this is what I have stored in a variable for Last Year Month Today and of course I only get the last mont in my selection.

Can I change it in some way to get the two selected months?

PeriodID = {$(=Max(PeriodID) - 12)},

Year = {$(=Max(Year)-1)},

Quarter = ,

Period = ,

Month =

Håkan

Re: Select moore months in Set Analys

adamdavi3s — Thu, 09 Mar 2017 10:15:38 GMT

OK so you're selecting Year and Month?

If so then

sum({<PeriodID={">=$(=Max(PeriodID) - 12)"}>}yourfield)

as this will in theory show all 12 months, but because you've selected month it will restrict it.

Sorry if I have missed the point the requirement isn't totally clear to me

Re: Select moore months in Set Analys

Thu, 09 Mar 2017 10:23:36 GMT

Hi,

I'm not sure if I understand your case correctly but If you just want to show value for selected months from the year before theoretically you don't have to use PeriodID. That should be enough:

sum({$<Year = {$(=Max(Year)-1)}>} Field)

If you select Year and Jan,Mar you will get sum from Jan and Mar from the last year.

BR,

Milosz

Re: Select moore months in Set Analys

Thu, 09 Mar 2017 10:24:04 GMT

If I choose like this:

I get only the value of mars in LYMTD when I use this, it works fine when you select only one

mont but not if you select more months.

PeriodID = {$(=Max(PeriodID) - 12)},

Year = {$(=Max(Year)-1)},

Quarter = ,

Period = ,

Month =

Re: Select moore months in Set Analys

hemachandran — Thu, 09 Mar 2017 10:29:32 GMT

HI,

Try This Expression

=sum({$<CstFiscalYear={$(=max(CstFiscalYear)-1)}>}UnitActual)

Dont use period PeriodID in your expression..

Re: Select moore months in Set Analys

adamdavi3s — Thu, 09 Mar 2017 10:30:52 GMT

Ok what is your formula for LYMTD?

Re: Select moore months in Set Analys

sasiparupudi1 — Thu, 09 Mar 2017 10:31:56 GMT

Use a period range

ex:

PeriodID = {">=$(=Max(PeriodID) - 12)<$(=Max(PeriodID))"}

Year =,

Quarter = ,

Period = ,

Month =

hth

Sas

Re: Select moore months in Set Analys

adamdavi3s — Thu, 09 Mar 2017 10:34:44 GMT

or just PeriodID={">=$(=Max(PeriodID) - 12) as i suggested above.

<$(=Max(PeriodID)) is a given, assuming of course you meant to write <=$(=Max(PeriodID))

Re: Select moore months in Set Analys

neha_shirsath — Thu, 09 Mar 2017 10:35:21 GMT

Hi,

I'm not sure i got your point correctly, means you want to show rolling 12 months?

sum({<cal_yr=,cal_month_abr_name=,cal_prd=,cal_prd_seq ={"<=$(=max(cal_prd_seq)) >$(=max(cal_prd_seq)-12) "},

>}Sales)

Thanks & Regards,

Neha S.

Re: Select moore months in Set Analys

adamdavi3s — Thu, 09 Mar 2017 10:36:14 GMT

In fact why not ditch the period totally, you don't need anything for it at all

PeriodID =,

Year = {$(=Max(Year)-1)},

Quarter = ,

Period = ,

Month =

Re: Select moore months in Set Analys

Thu, 09 Mar 2017 10:43:34 GMT

My intention is to show the values for febr and mars 2016 in MTD (works fine) and

the values for febr and mars 2015 in LYMTD.

The calculation for MTD is Sum( $(vNettoFörs)) and for the same period last year

Sum({$<$(vSetLYMTD)>} $(vNettoFörs)) where my values in vSetLYMTD is

PeriodID = {$(=Max(PeriodID) - 12)},

Year = {$(=Max(Year)-1)},

Quarter = ,

Period = ,

Month =

But in this case I get the value for mars 2015 not febr and mars 2015.

Håkan

Re: Select moore months in Set Analys

PrashantSangle — Thu, 09 Mar 2017 10:47:01 GMT

Hi,

Alter your variable add >=

vSetLYMTD is

PeriodID = {">=$(=Max(PeriodID) - 12)"},

Year = {$(=Max(Year)-1)},

Quarter = ,

Period = ,

Month =

Regards,

Prashant

Re: Select moore months in Set Analys

adamdavi3s — Thu, 09 Mar 2017 10:53:02 GMT

as I said above.....

Also you don't NEED that periodID set! Gah! Come on!! its already a given based on the year (Again as I said above)

vSetLYMTD is

PeriodID = ,

Year = {$(=Max(Year)-1)},

Quarter = ,

Period =

edited to remove month thanks mgrabara_bpx

Re: Select moore months in Set Analys

Thu, 09 Mar 2017 11:00:01 GMT

In this case do I get the whole 2015

Re: Select moore months in Set Analys

Thu, 09 Mar 2017 11:01:56 GMT

Hi,

Do you people even read anothers posts or just gathering points? This community is getting worse and worse.

Håkan - please check my expression, that should solve your problem

Adam Davies - this won't work because you disregarded "Month" so Håkan selection over months will be ommitted

BR,

Milosz

Re: Select moore months in Set Analys

adamdavi3s — Thu, 09 Mar 2017 11:06:34 GMT

Hi Milosz,

Your answer actually has exactly the same result at the original answer I posted

sum({<PeriodID={">=$(=Max(PeriodID) - 12)"}>}yourfield)

Its just a slightly different way of doing it.

There are many different ways of doing this, however you are correct in that month should not have been included in my answer, thank you for spotting that

Re: Select moore months in Set Analys

Thu, 09 Mar 2017 11:20:23 GMT

Hi,

Adam I agree with your solution but only when PeriodID is unique for year and month and going from "1 to n". I assumed that in Håkan's data it can be for example from 1 to 12 for every year repeating. But we don't know that actually. I think he could try both methods and check if values are correct.

BR,

Milosz

Re: Select moore months in Set Analys

adamdavi3s — Thu, 09 Mar 2017 11:36:41 GMT

Totally agree Milosz, without knowing the exact setup it is possible to have many different answers which leads to a lot of ambiguity.

p.s. I also agree with your comment re the community, I remember a time when you could help someone out without 43 other people jumping in to give the same answer. Its the points system IMHO it breeds this bad behaviour

Re: Select moore months in Set Analys

Thu, 09 Mar 2017 12:32:04 GMT

Hello Miloz,

yes, you are correct, this is the right answer, I misspelled when I tried it

Now it works fine.