topic Re: Prior month and value loop error in QlikView

Prior month and value loop error

Wed, 25 Nov 2020 16:16:04 GMT

All,

Would appreciate feedback on the following scenario. I have created a Dimension using Value Loop and would like to have it dynamically name the last twelve months from today's date(-1). Below is the set analysis I put together to accomplish this, however, it loses three months(Jan-Mar). Unsure of why.

Missing Months

Need

Re: Prior month and value loop error

swuehl — Fri, 13 Oct 2017 16:55:46 GMT

Seems to work for me:

=ValueLoop(1,12)	Month(AddMonths(Today(),-ValueLoop(1,12)))

1	Sep
2	Aug
3	Jul
4	Jun
5	Mai
6	Apr
7	Mrz
8	Feb
9	Jan
10	Dez
11	Nov
12	Okt

Re: Prior month and value loop error

swuehl — Fri, 13 Oct 2017 17:00:58 GMT

Even your expression seems to work.

=ValueLoop(1,12)	Pick(ValueLoop(1,12), Month(AddMonths(Today(),-1)), Month(AddMonths(Today(),-2)), Month(AddMonths(Today(),-3)), Month(AddMonths(Today(),-4)), Month(AddMonths(Today(),-5)), Month(AddMonths(Today(),-6)), Month(AddMonths(Today(),-7)), Month(AddMonths(Today(),-8)), Month(AddMonths(Today(),-9)), Month(AddMonths(Today(),-10)), Month(AddMonths(Today(),-11)), Month(AddMonths(Today(),-12)) )

1	Sep
2	Aug
3	Jul
4	Jun
5	Mai
6	Apr
7	Mrz
8	Feb
9	Jan
10	Dez
11	Nov
12	Okt

Seems like there is something different in your setting, so please post a sample QVW

Re: Prior month and value loop error

Fri, 13 Oct 2017 17:02:52 GMT

Stefan,

Thanks for the quick response, are you using the below as the expression?

pick(ValueLoop(1,12),Month(AddMonths(Today(),-ValueLoop(1,12))))

Re: Prior month and value loop error

Fri, 13 Oct 2017 17:25:21 GMT

I checked settings and had a restriction on my dimension that was limiting it. Thank you for your input and the much shorter syntax! Can you recommend any resources for becoming proficient at set analysis or is more or less learned through experience?