topic Re: Problems with time interval month in QlikView

Problems with time interval month

Thu, 26 Mar 2015 16:02:06 GMT

Hello everyone,

I just wrote about it but without success .... rephrase the question.

In an application I have to analyze the sales turnover of the past years and the current year.

For the past years I have used the following syntax

sum ({<= FV_Tipo, Year = {$ (= max ({1} Year) -2)}>} FV_Imponibile_FAT) turnover in 2013

sum ({<= FV_Tipo, Year = {$ (= max ({1} Year) -1)}>} FV_Imponibile_FAT) turnover in 2014

sum ({<= FV_Tipo, Year = {$ (vMaxAnno)}>} FV_Imponibile_FAT) turnover in 2015

and until everything is right

the problem is now in turnover in 2015 there are also invoices for the current month (for example March)

I turnover 2015 (formula below)

sum ({<= FV_Tipo, Year = {$ (vMaxAnno)}>} FV_Imponibile_FAT)

I would remove the invoices for the current month so:

sum ({<= FV_Tipo, Year = {$ (vMaxAnno)}>} FV_Imponibile_FAT) - sum ({<= FV_Tipo, Month = {$ (vMaxMese)}>} FV_Imponibile_FAT)

What should I put in place of the part highlighted in red so that the chief and take off from the total invoices for the current month?

Someone help me would know ... it's a bit urgent!

Thanks to all in advance.

Re: Problems with time interval month

rubenmarin — Thu, 26 Mar 2015 16:08:49 GMT

Hi Gian Paolo, sometimes using month in set analysis you're filtering by number when QV expects a string, and sometimes you're filtering by string and QV expects a number.

If both are string you'll need to add quotes:

sum ({<= FV_Tipo, Year = {$ (vMaxAnno)}>} FV_Imponibile_FAT) - sum ({<= FV_Tipo, Month = {'$ (vMaxMese)'}>} FV_Imponibile_FAT)

If QV expects a number you should change vMaxMese variable to return a number or create another variable that returns a number, then you can simplify your expression:

sum ({<= FV_Tipo, Year = {$ (vMaxAnno)}, Month = {"<$ (vMaxMese)"}>} FV_Imponibile_FAT)

Re: Problems with time interval month

Thu, 26 Mar 2015 16:28:41 GMT

Hi Ruben,

Thanks for the answer.

I start with this formula

sum ({<= FV_Tipo, Year = {$ (vMaxAnno)}>} FV_Imponibile_FAT)

which I think is correct.

I want to remove from this value the INVOICES (FV_Imponibile_FAT) in March of 2015

How do I?

Thanks

Gian Paolo

Re: Problems with time interval month

rubenmarin — Thu, 26 Mar 2015 16:39:02 GMT

Hi Gian Paolo, you tried my previous suggestions?

You can add as expression without title and see in column title how QV is translating the $-expansion variables

Re: Problems with time interval month

Thu, 26 Mar 2015 16:43:46 GMT

Hi Ruben,

I want to use this formula

= sum ({<= FV_Tipo, Year = {$ (vMaxAnno)}>} FV_Imponibile_FAT) - sum ({<= FV_Tipo, Month = {$ (vMaxMese)}>} FV_Imponibile_FAT)

but instead of the part in bold putting in March 2015, or better yet the current month and year.

It is clear?

Thanks

Gian Paolo

Re: Problems with time interval month

rubenmarin — Thu, 26 Mar 2015 16:46:47 GMT

Hi, only guessing but maybe:

sum ({<= FV_Tipo, Year = {$ (vMaxAnno)}>} FV_Imponibile_FAT) - sum ({<= FV_Tipo, Year = {$ (vMaxAnno)}, Month = {'$ (vMaxMese)'}>} FV_Imponibile_FAT)

sum ({<= FV_Tipo, Year = {$ (vMaxAnno)}>} FV_Imponibile_FAT) - sum ({<= FV_Tipo, Year = {$ (vMaxAnno)}, Month = {'$ (=vMaxMese)'}>} FV_Imponibile_FAT)

sum ({<= FV_Tipo, Year = {$ (vMaxAnno)}, Month = {"<$ (vMaxMese)"}>} FV_Imponibile_FAT)

sum ({<= FV_Tipo, Year = {$ (vMaxAnno)}, Month = {"<$ (=vMaxMese)"}>} FV_Imponibile_FAT)

Re: Problems with time interval month

Fri, 27 Mar 2015 11:25:07 GMT

you can change the syntax in this formula and say less the current month?

sum({<FV_Tipo=, Anno={$(vMaxAnno)}, MeseNum={"<$(vMaxMese)"}>} FV_Imponibile_FAT)

If "vMaxMese" is March insert for exemple "vMaxMese -1" until february .... if possibile but i no expert of Qlik View

Re: Problems with time interval month

rubenmarin — Fri, 27 Mar 2015 12:19:01 GMT

Hi Gian Paolo, that formula is asking less the current month: MeseNum={"<$(vMaxMese)"}, if you want previous expressions to do the same you can add this filter in those expressions:

sum({<FV_Tipo=, Anno={$(vMaxAnnoPrec)}, MeseNum={"<$(vMaxMese)"}>} FV_Imponibile)

And if you want always the cummulative you can add the clause to avoid the mese filter:

sum({<FV_Tipo=, Anno={$(vMaxAnnoPrec)}, MeseNum={"<$(vMaxMese)"}, Mese>} FV_Imponibile)

This will return the same result if the user selects 'jan and feb' and if the user select only 'feb'

Re: Problems with time interval month

Fri, 27 Mar 2015 13:49:22 GMT

It does not work!!! sorry sorry sorry i not understand .....

sum({<FV_Tipo=, Anno={$(vMaxAnno)}, MeseNum={"<$(vMaxMese)"}>} FV_Imponibile_FAT)

all invoices of 2015

and ok work

But I want all 2015 unless the current month .... which formula? i do not understand

sum({<FV_Tipo=, Anno={$(vMaxAnno)}, MeseNum={"<$(vMaxMese)"}, 1 and 2 >} FV_Imponibile_FAT)

sum({<FV_Tipo=, Anno={$(vMaxAnno)}, MeseNum={"<$(vMaxMese)"}, gen and feb >} FV_Imponibile_FAT)

sum({<FV_Tipo=, Anno={$(vMaxAnno)}, MeseNum={"<$(vMaxMese)"}, jan and feb >} FV_Imponibile_FAT)

On my example does not work???

Thanks

Re: Problems with time interval month

Fri, 27 Mar 2015 14:41:16 GMT

I would use this formula: all invoices 2015 - invoices current month

sum({<FV_Tipo=, Anno={$(vMaxAnno)}, MeseNum={"<$(vMaxMese)"}> } FV_Imponibile_FAT) - sum({<FV_Tipo=, Anno={$(vMaxAnno)}, MeseNum={$(vMese=mar)} FV_Imponibile_FAT)

In the expression in bold what I have to put to dump the current month?

Re: Problems with time interval month

rubenmarin — Fri, 27 Mar 2015 20:31:42 GMT

Ok, I made less than the selected month, if you want less than the current real month you should change vMaxMese variable to:

=Num(Month(Today()))

You can, but you don't need,substract the invoices for the current month, just ask in the expression all invoices less than the current month.

sum({<FV_Tipo=, Anno={$(vMaxAnno)}, MeseNum={"<$(vMaxMese)"}> } FV_Imponibile_FAT)

Sorry for misunderstanding and late answer. I also recomend to change variable name to vCurrentMese or something similar in your language.