This time i am comparing the original date field instead of Monthname.
see my expression
Thanks a lot for your help. Really Appreciate it.
But I need to be able to display date when the month fields are selected. Linking directly to the date field will work but I cannot display date in the dashboard, only month.
Is there anyway I can link this to month? I tried inline, but when Jan is selected, it took the sum of 2013 & 2014 Jan data.
Any solution to this?
Will this not only bring back a single day for the previous month? Or is it the case that all dates are actually the first of the month?
You may want to take a look at the working examples I have uploaded for period to date and prior period comparison:
The other thing that you tend to need to do when fixing some fields in set analysis is ignore selections in all the others, eg:
Hope that helps a bit,
Thanks for the reply. Yes, the case is that all dates are actually the first of the month. This is because there are actually no date field in the data and we need to create a dummy date for qlikview to work.
Please see attached file and data for your reference.
As i am running in a tight timeline on this, was desperately looking for a solution. Tried so many other solutions, there are still hiccups somehow along the way. Would be grateful if you could provide a direct solution to this. Thank you.
You look like you had got yourself in a bit of an over complicated situation there. There was a significant bug in the INLINE load - causing the field names to be mangled.
All of the derivation of date parts is much more easily done in script, rather than joining inline tables anyway. Also, you only need the one Date field as you can derive everything else from that. The load script then simply becomes:
Month(Date) as Month,
Date(MonthStart(Date), 'MMM-YYYY') as [Month Year],
Year(Date) as Year,
(biff, embedded labels, table is Sheet3$);
You can add a whole lot of other Date derivations if you like (quarter etc.) you will find code for these on the Community forum - or in the examples I posted links to above.
For simple month to date comparison you need only two variables set:
set vMaxMonthYear = =Date(max([Month Year]), 'MMM-YYYY');
set vPriorMonthYear = =Date(addmonths(max([Month Year]), -1), 'MMM-YYYY');
And then the expressions for the current month and prior month columns are simply:
The short coming of this is that the current month will most likely only be month to date (as you can't look into the future) and the prior month will be a full month. As all of your dates are in as day one then this is the best you can do anyway. If you had days in the data you would also want to derive the day number, in the load:
Day(Date) as Day,
Find the day for the max month:
set vMaxDay = =Day(Max(Date));
And then include that in the expressions also:
The only thing you would need to worry about there is months with fewer days, ie. on the 28th of Feb you would probably want to compare with the 31st of Jan - not the 28th.
You then also get into seasonality and number of weekends in any given month - and things start getting a lot more complicated. I digress.
Hopefully the load script amendments, variables and expressions at the top of this post will solve your immediate issues.
I've attached a working example as well.
Superb! It works very well! It look so much less complicated as compared to the other solutions.
I find that defining the date parts are very useful as well. Thank you very much for the detailed explanations.
Thanks for the Solution.. i was as well Looking for the same .. I got me this Qvw..
i as well Saw your Videos and Other Qvw... Can u tell any Place where i can get ur All Video and Qvw to
Learn More about Qlikview