Store data on the basis of Year and Month dynamically

[pra_kale]

Hi,

While storing data in a .csv or .xls file  how i can pass year and month dynamically.

My year is on the basis of Financial year and month also according to this. For e,g. April is a 1 month, May is 2 and March is 12 and year is current year is 2017 which will change in April as 2018.

I have written a below given code to save the file but, in that i have hard-coded the Year and Month.

But i want to pass this year and month through variable based on the current year and month. For e.g. Upto 1 st of every month data will get saved up to last month. It means up to 1st September 2017 data get saved for August month i.e. year 2017 and for month 5 but from 2nd September data will get saved for the month 6 i.e. for September and so on.

Can you please help to resolve the issue..

Thanks in advance.

let vMonth=5;

let vYear=2017;

A:

LOAD NOP,

     Premium,

     Year,

     Month

FROM

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

B:

LOAD * Resident A Where Year = $(vYear) and Month = $(vMonth);

DROP Table A;

STORE B into E:\Qlikview\year_month.csv(txt);

[sunny_talwar]

Try these two variables:

LET vMonth = Pick(Month(/*Today()*/ MakeDate(2016, 9, 2) - 1), 10, 11, 12, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);

LET vYear = If(Month(Today() - 1) <= 4, Year(Today()), Year(Today()) + 1);

[pra_kale]

Thanks sunny... it is working after removing MakeDate(2016, 9, 2) - 1). as this formula giving me month  = 6.

It means i have kept vMonth=Pick(Month(Today()- 1), 10, 11, 12, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12); formula.

But at start of the month for e.g. at 1 sep 2016 my current month will become 6 but that time i required entire last month data,month  = 5, but from 2 sep 2016 till 1 oct 2016 i will require data for month = 6 and from 2 oct 2016 till 1 nov 2016 i will require data for month = 7..and so on.

so shall i need to hard core the month at every 1 st.

[sunny_talwar · Accepted solution]

Sorry, i was checking the expression... You need this...

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

[pra_kale]

Thanks sunny...great...it is working.