topic Re: Show last 6 months when selecting in QlikView

Show last 6 months when selecting

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

hello,

i would like to show the last 6 months when selecting a date.

date is my dimension and its format is yyyymm

sales is an expression

sum({1<date={'>=$(=AddMonths(Max(date),-5)) <=$(=(Max(date)))'}>}sales)

it is not working and i do not understand why.

i hope you can help me.

Re: Show last 6 months when selecting

vishsaggi — Wed, 25 Jan 2017 20:13:24 GMT

1 is the identifier in your set to ignore current selections.

Try removing 1 and select date and see if you getting any results?

= sum({<date={ ' >= $(=AddMonths(Max(date),-5)) <= $(=Max(date)) ' }>} sales)

Re: Show last 6 months when selecting

Anonymous — Wed, 25 Jan 2017 21:01:16 GMT

not working

Re: Show last 6 months when selecting

vishsaggi — Wed, 25 Jan 2017 21:05:37 GMT

Try this

= sum({<date={ " >= $(= Date(AddMonths(Max(date),-5))) <= $(=Max(date)) " }>} sales)

= sum({<date={ " $(= '>=' & AddMonths(Max(date),-5)) & '<=' & Max(date)) " }>} sales)

Can you share a sample app if possible?

Re: Show last 6 months when selecting

Anonymous — Wed, 25 Jan 2017 21:11:03 GMT

mm i think that date is making it wrong.

¿it is always my dimension ?

Re: Show last 6 months when selecting

Anonymous — Wed, 25 Jan 2017 21:18:34 GMT

here i attached you some sample data

Re: Show last 6 months when selecting

vishsaggi — Wed, 25 Jan 2017 22:26:37 GMT

Can you send me correct dates, when i used max date i got 210607 is this correct ? If i do addmonths -5 it is giving 210602 which is not there in the data.?

Re: Show last 6 months when selecting

Anonymous — Wed, 25 Jan 2017 22:32:28 GMT

here you have with different data.

Re: Show last 6 months when selecting

vishsaggi — Wed, 25 Jan 2017 23:16:56 GMT

When ever we deal with dates from excel we need to convert them to proper dates and work on them, that is how i do personally else things wont work properly. So, i have created my own excel sheet and worked on it. I have also created couple of variables for ease. Just press ctrl+alt+v to view them. Please find attached the excel sheet that replicates your data and sample file i worked on. Let me know if this is what you are looking for?

Re: Show last 6 months when selecting

Anonymous — Wed, 25 Jan 2017 23:44:35 GMT

it works fine. thank you