topic How to change date to MonthStart? in QlikView

How to change date to MonthStart?

Thu, 30 Mar 2017 17:11:25 GMT

Hello,

The following is what we're using to create our date fields. How would I manipulate this to be the month start date?

Month(MakeDate(left(right('$(vVulnerabilityFile)',11),4),left(right('$(vVulnerabilityFile)',6),2))) AS MNTH,

Thanks!

Re: How to change date to MonthStart?

sunny_talwar — Thu, 30 Mar 2017 17:17:01 GMT

May be this

Date(MakeDate(left(right('$(vVulnerabilityFile)',11),4),left(right('$(vVulnerabilityFile)',6),2))) as MonthStart

Re: How to change date to MonthStart?

Anil_Babu_Samineni — Thu, 30 Mar 2017 17:17:27 GMT

What does this vVulnerabilityFile? And if Date would you create few dates so then we came to know. Seems complex we can achieve by default functions may be

Re: How to change date to MonthStart?

vishsaggi — Thu, 30 Mar 2017 17:18:48 GMT

What is the value you hold in vVunerabilityFile variable like i mean what is the date format that this variable holds.

You can use MonthStart(vVunerabilityFile) may be?

Re: How to change date to MonthStart?

Thu, 30 Mar 2017 18:29:51 GMT

this worked, thank you. I'm trying to limit a pie chart to only display one month worth of data. this is what i'm using as my expression but it's saying no data to display:

Sum({<MonthStart={$(=Max(Month))}>}CRITICAL)

Sum({<MonthStart={$(=Max(Month))}>}HIGH)

any ideas?

Re: How to change date to MonthStart?

sunny_talwar — Thu, 30 Mar 2017 18:31:32 GMT

Add double or single quotes around...

Sum({<MonthStart={"$(=Max(Month))"}>}CRITICAL)

Sum({<MonthStart={"$(=Max(Month))"}>}HIGH)

Re: How to change date to MonthStart?

Thu, 30 Mar 2017 18:39:09 GMT

didn't work

Re: How to change date to MonthStart?

sunny_talwar — Thu, 30 Mar 2017 18:41:48 GMT

My bad, you need to do Max(MonthStart)

Sum({<MonthStart={"$(=Max(MonthStart))"}>}CRITICAL)

Sum({<MonthStart={"$(=Max(MonthStart))"}>}HIGH)

Re: How to change date to MonthStart?

vishsaggi — Thu, 30 Mar 2017 18:41:57 GMT

Sunny, i believe Ann is comparing Date with Month here right? I mean

MonthStart = Max(Month) ?

Re: How to change date to MonthStart?

Thu, 30 Mar 2017 18:44:40 GMT

It's still not working

Re: How to change date to MonthStart?

sunny_talwar — Thu, 30 Mar 2017 18:51:39 GMT

These did not work?

Sum({<MonthStart={"$(=Max(MonthStart))"}>}CRITICAL)

Sum({<MonthStart={"$(=Max(MonthStart))"}>}HIGH)

Re: How to change date to MonthStart?

Thu, 30 Mar 2017 19:01:03 GMT

Nope. Maybe I should clarify what i'm trying to obtain.

I want a pie chart that shows critical, highs, and lows for the previous month.

If I do sum(CRITICAL), etc it will work just fine, but that is for all time. How do I get it to just show for the previous month?

Re: How to change date to MonthStart?

vishsaggi — Thu, 30 Mar 2017 19:02:20 GMT

What is the format of your MonthStart? Is it MM/DD/YYYY ?

Re: How to change date to MonthStart?

Thu, 30 Mar 2017 19:03:52 GMT

yes

Re: How to change date to MonthStart?

sunny_talwar — Thu, 30 Mar 2017 19:04:10 GMT

Previous should be this

Sum({<MonthStart={"$(=AddMonths(Max(MonthStart), -1))"}>}CRITICAL)

Sum({<MonthStart={"$(=AddMonths(Max(MonthStart), -1))"}>}HIGH)