topic Re: Expression in QlikView

Expression

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

Hi All

I have bar chart which should display always 9 months from current date.

this is December month therefore it should display from dec to April.(4 to 12)

The month is in number format it displays for Jan- 1, Feb-2....and so on ... as shown in the the screenshot.

Iam using the the below expression expression to display 9 month data ..

Please correct me ..this is not working .

The dimension is Month

expression :

Sum ({<Month={">=$(=date(addmonths(Month,-9),'MMM-YYYY'))<=$(=max(date(addmonths(Month,0)),'MMM-YYYY'))"}>}Tickets)

Thanks in advance

Re: Expression

Anil_Babu_Samineni — Wed, 06 Dec 2017 06:19:31 GMT

If, Max(Month) returns 12 then should be this?

Sum({<Month = {">=$(=Max(Month)-9) <=$(=Max(Month))"}>} Tickets)

Re: Expression

smilingjohn — Wed, 06 Dec 2017 06:22:19 GMT

Hi Anil

Thanks for the reply ..

Next year it will show jan ..

So will this still diplay 9 month from Jan-2018 back to May-2017?

totally nine months from current date of Month...

Thanks

Re: Expression

its_anandrjs — Wed, 06 Dec 2017 06:30:52 GMT

Do you have date field in data model if so i suggest

Sum ({<Datefield={">=$(=date(addmonths(Datefield,-9))) <=$(=max(date(addmonths(Month,0))))"}>} Tickets)

Re: Expression

Anil_Babu_Samineni — Wed, 06 Dec 2017 06:38:50 GMT

100% agree, But in your image it is showing as numbers for month. But, In your statement using MMM-YYYY format for Month to degrade. What does you mean that?

Re: Expression

smilingjohn — Wed, 06 Dec 2017 07:06:45 GMT

Hi Anil...

Your Expression works for 9 months ...and the data is accurate..

So i am going ahead with this..

My only question will it always show nine months even if it eneters the next year jan or Feb ?

Thanks

Re: Expression

Anil_Babu_Samineni — Wed, 06 Dec 2017 07:09:13 GMT

Yes, It is? But the concept we need to understand the concept of Forecast data. that is the case you need to bifurcate till today only not in forecast data like

Sum({<Month = {">=$(=Max(Month)-9) <=$(=Num(Month(Today())))"}>} Tickets)

Re: Expression

smilingjohn — Wed, 06 Dec 2017 07:26:57 GMT

Thanks Anil

got it.

Just i have one more query .

In one of my other chart the date format is like 1/1/2017 as shown in the screenshot.

and here also i need to show till nine months ...

with your logic i tried to use expression like below

Sum({<Month = {">=$(=Date(Max(Month))-9) <=$(=Date(Max(Month)))"}>} Tickets)

but this doenst give me the proper result ..

do i need to make any specific change here ?

Thanks

Re: Expression

luismadriz — Wed, 06 Dec 2017 07:47:53 GMT

Hi,

If you're worried about year then you need to make sure your definition of Month has that concept.

You could use something like this to define Month:

Dual(Year(DateField) & '-' & Month(DateField), MonthStart(DateField)) as YearMonth or something prettier...

Once the field has been dualed with a number that recognises the year and month, then you can think of substracting a number of Months as you've been advised in the other posts

I hope this helps,

Cheers,

Luis

Re: Expression

Anil_Babu_Samineni — Wed, 06 Dec 2017 08:24:37 GMT

Not entirely sure, What are you going here. Even thought i have like

Sum({<DateField = {">=$(=Date(AddMonths(Max(DateField),-9),'Your Date Format Here')) <=$(=Date(Max(DateField), 'Your Date Format Here'))"}>} Tickets)

Some cases, This may works or won't work if we have missed date fields with at least 0 values. So, I would always generate Calendar script to date field for all dates. And, Finally this should work if MonthStart Date like

Sum({<DateField = {">=$(=Date(MonthStart(AddMonths(Max(DateField),-9)),'Your Date Format Here')) <=$(=Date(Max(DateField), 'Your Date Format Here'))"}>} Tickets)

Note - If applicable, Please mark it as helpful and correct answer to close this thread.

Re: Expression

its_anandrjs — Wed, 06 Dec 2017 08:30:20 GMT

Simple use but no need to change date format if required then change.

Sum ({<Datefield={">=$(=Date(addmonths(Datefield,-9))) <=$(=max(Date(addmonths(Month,0))))"}>} Tickets)