sum of a specific month

[Anonymous]

hello Comunity,

I have  a dataset of sell itens. eacho of the lines is a unique sell item. the two rows that are important is the value (Valor.Face.Título) and the date that sell was registered (Mês.Criação.Título).

this dataset is refreshed weekly

I need to create a KPI showing the total value for a specific month. this specific month is the refresh date month.

i used the formula below and this works just fine:

Sum({$<[Mês.Criação.Título]={"dez 2018"}>} [Valor.Face.Título])

the problem is that I have to adjust all the formulas when the month turnr (I.E. when I have the refresh for January 2019, i'll have to change "dez 2018" to "jan 2019")

I've been trying to use a variable set in the script as vMês.Extração and use it make it easier to update the formula as below:

Sum({$<[Mês.Criação.Título]={vMês.Extração}>} [Valor.Face.Título])

but it doesn't work. bring back zero as result

any advice?

thak you 

Giordano

[Somasundaram]

Hi,

you can declare variable as vmth=date(Today(),'MMM-YYYY')

Sum({$<[Mês.Criação.Título]={$(vmth)}>} [Valor.Face.Título])

[dwforest]

Make sure the date formats defined are the same so that Qlik knows its a date and not a string.

SET DateFormat = "MMM YYYY";

or in var declaration:

=Date(Today(),"MMM YYYY");

[Anonymous]

Tried this but unfortunately didn't work. 

I forgot to mention that I'm using qliksense desktop.

That field [Mês.Criação.Título] is created on the script, by the code line below:

Date([Data.Criação.Título],'MMM YYYY') as Mês.Criação.Título,

where [Data.Criação.Título] is a DD/MM/YYYY date

so, it should work, shouldn't it?

[dwforest]

Not necessarily, it depends on the value of DateFormat, which would have to be set to 'DD/MM/YYYY' in order for Qlik to recognize it as a date.

you could do:

Date(Date#([Data.Criação.Título],'DD/MM/YYYY'),'MMM YYYY') as Mês.Criação.Título

To get the date number of Data.Criação.Título from its DD/MM/YYYY format and convert the resulting number to 'MMM YYYY'

Desktop vs Enterprise should make no difference

[Anonymous]

good help. but still not working  😐

With this code, the [Mês.Criação.Título] may be right now. but the variable neither has the same format nor matches the criteria to make the sum works..... 

any advice about setting the variable? this seems to be where the problem is now...

a broader question: is it better to set the variable on the script or 'inside' the app?

thanks,

[dwforest]

This variable is set and included by default in your script.

Just change the existing value to the one you want/need.

For variables in general it doesn't matter if defined in script or in app; mostly depends on where the variable is needed.

Don't know if variables defined in app are available in the script part though...