Values for current month and last Month in the same table

[nancybricenoa]

Hello.

I have a table with values for current month (that it is a month selected by user in QV and in the same table I have to show the values for month before to selected.

I have a table with all Dates and Date_before like this:

Date_Extraction	Fecha_Ant	Order
20191031	20190930	10
20190930	20190831	9
20190831	20190731	8

And the Date:Extraction is match with file with values.

So, If I select Sep-19 in the table I need to show Sum(value) for Aug and sum(value) for Sep.

Any help?

Thanks

Nancy

[Arthur_Fong]

Try this:

Script:

Raw:  //Replace this with your production table
load * inline [
Date_Extraction,Fecha_Ant,Order
20191031,20190930,10
20190930,20190831,9
20190831,20190731,8
];

Data:
load *,
MONTH(Date_Extraction)&'-'&YEAR(Date_Extraction) as YearMonth,MONTH(Date_Extraction)as Month;
load Date#(Date_Extraction,'YYYYMMDD')AS Date_Extraction,Date#(Fecha_Ant,'YYYYMMDD')as Fecha_Ant,Order
resident Raw;
drop table Raw;

Expression:

Sum(Order)+
Sum({<Month={"$(=Month(Date(AddMonths(max(Date_Extraction),-1),'YYYYMMDD')))"},YearMonth=>}Order)

Thanks and regards,

Arthur Fong

[nancybricenoa]

Hi!

thanks for your reply but, I need to sum (value) for last month and in anothe column sum(value) for this month. also I have another restriction .

currently my expression is:

=sum(IF(Variable='AR', Valor,0))/1000

Here I need to indicate that this sum mus be showed in a colum for date selected and another colum with valor for dete before to selected one

Thanks!

[Brett_Bleess]

Here are a couple of Design Blog posts that coincide with where Arthur was headed with things that should help:

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344

https://community.qlik.com/t5/Qlik-Design-Blog/Year-over-Year-Comparisons/ba-p/1462927

The latter is a year over year, but figured it should be able to be adjusted to month...  That is the reason I grabbed it, sorry if it is of no help.  In the first link, there are some additional Blog links at the bottom of that one to related set analysis topics, just FYI.

Also including Help link on Set Analysis too:

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/ChartFunctions/...

Regards,
Brett