topic Re: Prior year for year_month field in QlikView

Prior year for year_month field

Mon, 11 Apr 2016 08:57:24 GMT

Hi all,

sorry for this newbie question but somebody can explain me how to get the prior year (same month) value for the field Year-Month?

Example: 2016-02 I have to see in the Prior column the value for 2015-02.

Thanks in advance!

Regards

Re: Prior year for year_month field

Kushal_Chawda — Mon, 11 Apr 2016 09:21:47 GMT

try below expression for Prior Month. Make sure that YearMonth Field is not a string

=sum({<Year_Month= {"$(=date(addmonths(max(Year_Month),-1),'YYYY-MM'))"}>})Amount

Re: Prior year for year_month field

sunny_talwar — Mon, 11 Apr 2016 09:43:06 GMT

You can try this approach as well:

Vol_Avanz_GG:

LOAD Date#(Year_Month, 'YYYY-MM') as Year_Month,

Qta

INLINE [

Year_Month, Qta,

"2015-01", 20,

"2015-02", 210,

"2015-03", 2310,

"2016-01", 10

"2016-02", 10

"2016-03", 10

"2014-02", 10

];

Link:

LOAD Year_Month as ReportYearMonth,

Year_Month,

'CY' as Flag

Resident Vol_Avanz_GG;

Concatenate(Link)

LOAD Year_Month as ReportYearMonth,

AddYears(Year_Month, -1) as Year_Month,

'PY' as Flag

Resident Vol_Avanz_GG;

Expressions

1) =Sum({<Flag = {'CY'}>}Qta) -> Current Year

2) =Sum({<Flag = {'PY'}>} Qta) -> Prior Year

Data Model

Re: Prior year for year_month field

Mon, 11 Apr 2016 10:57:03 GMT

Thanks Kushal, but this set analysis will work just for one Year_Month, the 2016-02, that is in fact the Max(Year_Month) - 1 of my whole data:

Actually I need that the calculation is made for all the rows.

Thanks.

Giovanni

Re: Prior year for year_month field

Mon, 11 Apr 2016 10:59:03 GMT

Thanks Sunny.

This soluction is working but... ii will perform 3 load of table Vol_Avanz_GG, that is made of 4M+ of rows.

Regards,

Re: Prior year for year_month field

sunny_talwar — Mon, 11 Apr 2016 13:04:49 GMT

I don't find this to be an issue just because you won't be repeating the 4M+ rows, but just repeat the dates twice, which should not be too many rows of data

Re: Prior year for year_month field

Mon, 11 Apr 2016 14:57:56 GMT

Hi Sunny, your solution is working but i want see also the previous year when i select one year.

For example: if i select 2016-01, i want see in the table also 2015-01.

With your solution 2015-01 is zero.

can somebody help me?

Re: Prior year for year_month field

sunny_talwar — Mon, 11 Apr 2016 15:44:28 GMT

I don't think that should happen, but let me test it out

Re: Prior year for year_month field

sunny_talwar — Mon, 11 Apr 2016 15:46:30 GMT

I would not make selection in Year_Month field and instead make selection in ReportYearMonth field

Re: Prior year for year_month field

Tue, 12 Apr 2016 07:46:58 GMT

Thanks Sunny, now your solution is working correctly