topic Re: Subtracting dates with aggr function in QlikView

Subtracting dates with aggr function

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

I have a chart (PivotTable). In it, as dimensions, I display the fake columns "Days1_" - = (Interval (Today () - date_task, 'd')) - and "Media_" - =(Round (aggr (avg (interval (today () (data (today ())) d ')), city_task, burgh_task, phase_task, my_task))).

The values are properly displayed.

But when I create a fake column "Dif_" -

=
(Interval (Today () - date_task, 'd'))
-
(Round (aggr (avg (interval (today () - data_task, 'd'))), city_task, burgh_task, phase_task, my_task))

The result is null. Why?

My ultimate goal is to set background colors (if days1_> media_1, red; if days1_ <media_, green; if days1_ = media_, yellow), but with result null, the test for setting colors doesn't work ...

Thanks in advance for any help.

Re: Subtracting dates with aggr function

Anil_Babu_Samineni — Thu, 08 Aug 2019 16:51:59 GMT

Is that syntax issue? Or can you share what you are getting using 2 different expressions

=(Interval (Today () - date_task, 'd'))-(Round (aggr (avg (interval (today () - data_task, 'd'))), city_task, burgh_task, phase_task, my_task)))

Please remove red parenthesis and add it in the blue color

Re: Subtracting dates with aggr function

luiz_damascena — Thu, 08 Aug 2019 17:12:01 GMT

I wrote wrong - the definition of "Media_" is

(Round (aggr (avg (interval (today () - data_task, 'd'))), city_task, burgh_task, phase_task, my_task))

Re: Subtracting dates with aggr function

luiz_damascena — Thu, 08 Aug 2019 17:17:45 GMT

Thanks, but I believe that I wrote wrong, in the post... in my qvw, the definition is like you wrote, with the parenthesis in the correct position.

Re: Subtracting dates with aggr function

Anil_Babu_Samineni — Thu, 08 Aug 2019 17:28:16 GMT

What is the out put you've got using both expressions?

Re: Subtracting dates with aggr function

luiz_damascena — Thu, 08 Aug 2019 17:33:38 GMT

The output is "-", that is the defined as the Null Symbol in Presentation Tab.

Re: Subtracting dates with aggr function

Anil_Babu_Samineni — Thu, 08 Aug 2019 17:35:48 GMT

Can we have sample application to test ?

Re: Subtracting dates with aggr function

luiz_damascena — Thu, 08 Aug 2019 17:44:40 GMT

Thanks again, but in this case, I don't know how to do that... it´s a big qvw, with a lot of code on script... I don't know how to generate data to mount the test, to send... I'm sorry, and thanks again.

Why can I see the colums but I can't do a math operation using them? Mind blowing!

Re: Subtracting dates with aggr function

Brett_Bleess — Tue, 27 Aug 2019 13:56:15 GMT

Luiz, you can use the File\Reduce Data\Keep Possible in the Desktop client, but be sure you make some selections first to be sure there is some data still left in the data model. If there are any confidential fields as well, use the Settings\Document Properties\Scrambling tab to scramble those...

Regards,
Brett