topic Re: Need to add a flag on Jan file dynamically in QlikView

Need to add a flag on Jan file dynamically

Mon, 29 Feb 2016 01:36:50 GMT

Hi Folks/ sunindia‌,

Thanks for all the help in the past.

I have a small issue which I'm unable to fix. Hope somebody could help me if, its not a lot of trouble.

I have a bunch of files which I receive from the client every month. And in one of my column in the chart I want to show data only from Jan file irrespective of any month selected.

Say, if any month from 2015 is selected I want to show data from 2015 Jan file.

If any month from 2016 is selected I want to show data from 2016 Jan file.

2015 Jan - 2015 Dec, result should be 22238

2016Jan - 2016Feb, result should be 41184

Basically I wish to add a dynamic flag for the Jan file.

Can anybody show me how to achieve it.

Re: Need to add a flag on Jan file dynamically

sunny_talwar — Mon, 29 Feb 2016 02:15:16 GMT

See if the attached is helpful

Re: Need to add a flag on Jan file dynamically

Mon, 29 Feb 2016 02:42:21 GMT

Thanks for the quick reply Sunny.

But when I make any selection apart from Jan 2015, it's showing zero. I want it to display 22238 for any month in 2015. And for anything selected in 2016 I want it to display 41184.

Hope it makes sense.

Re: Need to add a flag on Jan file dynamically

sunny_talwar — Mon, 29 Feb 2016 02:47:53 GMT

Try this expression:

=Sum({<FileMonthNum = {1}, FileName>}Value)

Re: Need to add a flag on Jan file dynamically

Mon, 29 Feb 2016 03:17:37 GMT

Sunny when I select any other month like Jun 2015 its showing 63422 whereas it should display 22238. In my original solution I would be joining this table with my other fact table on Filename. So I need to select FileName.

Re: Need to add a flag on Jan file dynamically

sunny_talwar — Mon, 29 Feb 2016 03:39:14 GMT

I am not sure what is missing, did you create a new field called FileMonthNum? and use that in your expression?

Re: Need to add a flag on Jan file dynamically

Mon, 29 Feb 2016 07:15:21 GMT

Hi Aditya,

Try changing the expression given by Sunny as below

if(GetSelectedCount(FileName)>0,num(sum({<FileName={"$(=Date(YearStart(FileName),'MMM YYYY'))"}>}Value),'##,###.##'),

sum({<FileMonthNum={1},FileMonth,FileName>}Value))

Regards