Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

YearMonth chart with macro and set analysis which shows also missing data, is it possible?

Hi,

I have tricky problem with YearMonth Chart! You can select a time period via macro for example Jan-2007 to Dez-2007 and save it via macro to a variable. The expression in the chart includes the time range from the variable, and a modifier which says take only data from Type = A . But there is my problem, than for the months in the year 2007 for Aug, June and April there is no Data of the Type A, but I want that the chart shows me also the missing bars for this months with null as sum.

Is it possible to solve this problem and when yes please give me the solution!

I have prepared an adequate sample where you can try to solve it.

Thanks for helping me!

1 Solution

Accepted Solutions
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

6 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Herbert,

I'm not sure that I understand what you are trying to do with your dates, but the problem (as I see it) is because you Date1 and Date2 are both linked to the Type. When there are no records for a specific Type, Date2 is not "available" and therefore it doesn't make it to the selection list within Group1.

If you detach Date2 into a separate list, not related to the Type, you should be able to achieve what you are trying to achieve.

Oleg

Not applicable
Author

Thanks for answer, I have tried to make something like Oleg said, but I came to no solution, maybe you Oleg can take my sample application and make some changes to it, that will be great.

And to explain my problem in short form, my endresult which I want to get is a dynamic chart, I tried it before with a complex set analysis function, you can see it as second expression in the formula at the chart, and now I tried it with macro and variables and with both I had the same problem.

Yes there is no data for the specific Type not every MonthYear has a Type A and not every a Type B, In my real application I also have a calendar. So all the dates exist (days, weeks, years, months, YearMonths....) also where no data exists.

I think the way I am trying to solve my problem is not right, all i want is a dynamic chart for example when I select a month I would get the data of the last 12 months, ==> selection of Nov-2007 than the chart should include a date range from Dez-2006 to Nov-2007, the bars for every month should always be there (12 bars) and if there is no data there should be 0 when I use a count or a sum function.

Or is there a solution how I can change the sample data in a way so that qlikview recognizes that there is null as result also without real data, or should I add in some way any extra data fields?

I hope I can get some suggestions?

The application is in my first post you can download it.

Kind regards,

Herbert

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Not applicable
Author

Hi Oleg,

Thank you very very much, I like your solution and the best it is without macros and set analysis and I can write the motifications directly within the if formula in the chart, like Type = 'A'

But maybe you can give me some further input about the formula in the dynamic dimension.

=if(num(CalDate+365) > num(Date3) and num(CalDate) <= num(Date3), date(CalDate, 'MMM-YYYY'), null())

Can you explain me in short form what the formula does in every part, how it comes to the lasts 12 months, and how I can use it when I would like to have other date ranges lets say the last 3 months or the last 6 months, so I can use it in differnt form, I think I have to change the +365 but how?

Thanks Oleg it works fine.

Kind regards

Herbert

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Herbert,

each date is stored as a number, and each day = 1. So, if we use Date3 as a selection field, the condition:

num(CalDate+365) > num(Date3) means that the Calendar Date should be greater than the selected date - 365 days (or 12 months)

num(CalDate) < num(Date3) means that the Calendar Date should be less than the selected date (to exclude future days).

I used function num() to ensure that we are comparing numeric values, disregarding the date format. I think, those can even be omitted (I added them when something didn't work as I expected).

The if() function will return null() if the condition is false. Null() values are suppressed using the checkbox "Suppress when value is null()"

Oleg

Not applicable
Author

Thank you Oleg for the explanation now I think I could use this solution in different way in my other applications. Have a nice weekend.

Kind regards

Herbert