topic Re: Usage between dates in QlikView

Usage between dates

Mon, 03 Nov 2014 16:37:52 GMT

Hi,

i am facing a problem while calculating the usage between two dates.

i have two dates.& Days fields.

vReportDate - defined as a variable

StartDate - calculated as an expression inside a straight table.

Days - calculated as an expression inside a straight table.

My expressions:

> Days = mod(fabs(Date( vReportDate,'DD/MM/YYYY')) - fabs(Date([Activation Date],'DD/MM/YYYY')),30)

> StartDate = Date(vReportDate - [Days],'YYYY-MM-DD')

> Total Usage =

SUM(IF([Air Bag Tracking Date] >= [StartDate]

AND [Air Bag Tracking Date] <= DATE(vReportDate ,'YYYY-MM-DD')

AND [IN_COUNTER_TYPE] = 'AC.Airbag_Voice_Min_Daily_Cap_Counter'

AND [IN_UPDATE_TYPE] = 2 ,[Air Bag Amount]))

i can successfully build the expressions for Days and startDate, but when i am trying to use them in another expression to calculate the total usage between StartDate and vReportDate, the output is ZERO.

However i try changing the formats of the date, its still the same..i am badly stuck here.

Any Help would be deeply appreciated.

Thank you.

Re: Usage between dates

Mon, 03 Nov 2014 16:45:06 GMT

It could be treating one as date and other as number. Can you upload a sample file?

Re: Usage between dates

JonnyPoole — Mon, 03 Nov 2014 16:45:47 GMT

Likely your if() statement is always evaluating to false. Its PROBABLY (not sure) something to do with '([Air Bag Tracking Date] >= [StartDate]' part of the condition.

You could try putting this in an expression SUM(IF([Air Bag Tracking Date] >= [StartDate] , [StartDate] ,[Air Bag Tracking Date]) to figure out what this is evaluating too in your expression. If you see something unexpected you'll need to tweak the formula. Post the result or the whole QVW so it can be directly evaluated.

Re: Usage between dates

Tue, 04 Nov 2014 02:37:19 GMT

Hi Jonathan,

Thanks for your response.

I have checked with the expression SUM(IF([Air Bag Tracking Date] >= [StartDate] , [StartDate] ,[Air Bag Tracking Date])) and I see some numbers which I am not sure of.

Yes, I understand the problem is with the '([Air Bag Tracking Date] >= [StartDate]' expression. i am stuck here.

Any suggestions or alternative logic to handle the situation. Please assist me.

Attaching the sample qvw for your assistance. Please refer to the “AirBag Voice(Mins) - New Activations” straight table.

Re: Usage between dates

JonnyPoole — Tue, 04 Nov 2014 03:58:17 GMT

Hi - i had to remove the 'Days1' reference from the AirBag(mins) expression. It didn't like it for some reason. When you right out the whole expression for Days1 instead it works fine...

Consider making Days1 as a variable if you want to manage the expression in one place, just because this make you have the definition in 2 places.

Here is the modified expression:

-SUM( IF( Date([Air Bag Tracking Date]) >= Date(vReportDate - mod(fabs(Date( vReportDate,'DD/MM/YYYY')) - fabs(Date([Activation Date],'DD/MM/YYYY')),30) )

AND Date([Air Bag Tracking Date]) <= DATE(vReportDate ,'YYYY-MM-DD')

AND [IN_COUNTER_TYPE] = 'AC.Airbag_Voice_Min_Daily_Cap_Counter'

AND [IN_UPDATE_TYPE] = 2 ,

[Air Bag Amount]))/1000/60

Re: Usage between dates

Tue, 04 Nov 2014 06:13:48 GMT

Thanks alot jonathan,

its working now. you saved my day.

Thank you.