topic Re: conditional sum based on two input box in QlikView

conditional sum based on two input box

Wed, 15 Apr 2015 16:40:08 GMT

Dear all,I have a straight table with 3 dimension (category, company and business unit) and an expression that is the daily balance.

My need is to compare the balance between two different dates.

I've created two variables and two input box date 1 and date 2.

I then used two expression for each input box like this one:

=SUM({$<DATE_REF={"$(=VAR_DATA2)"}>}Balance_CTV2)

=SUM({$<DATE_REF={"$(=VAR_DATA1)"}>}Balance_CTV2)

but the result is always "0" even the data exist.

I've laso tried without the " but always the same 0.

What is wrong in my expression?

Thank you

Re: conditional sum based on two input box

Wed, 15 Apr 2015 16:44:46 GMT

You must convert the variables string to date try this:

=SUM({$<DATE_REF={"$(=Date(VAR_DATA2))"}>}Balance_CTV2)

=SUM({$<DATE_REF={"$(=Date(VAR_DATA1))"}>}Balance_CTV2)

Regards

Re: conditional sum based on two input box

Wed, 15 Apr 2015 16:52:05 GMT

Dear Gennaro,

the variables are already defined with date format but even using your suggested sintax the result still the same

Re: conditional sum based on two input box

Wed, 15 Apr 2015 16:53:48 GMT

You can send an example (qvw) ?

Re: conditional sum based on two input box

Wed, 15 Apr 2015 16:54:04 GMT

Can you please provide the definition of variables you declared. Should be an issue with your variable declaration.

Thanks,

Sai

Re: conditional sum based on two input box

Wed, 15 Apr 2015 17:01:18 GMT

Dear All,

please find attached the qwv. The tab with the comparison is the last one.

Thank you for your assistance.

regards

Re: conditional sum based on two input box

Wed, 15 Apr 2015 17:03:45 GMT

Looks the attachment is missing.

Re: conditional sum based on two input box

Wed, 15 Apr 2015 17:05:28 GMT

Dear vsaikrishna,

Hope the qvw could answer your question. Anyway on hte input box into the number tab I've just set the format to DATE. Is the right way or there is an other point where to declare the variable?

Thank you

Re: conditional sum based on two input box

Wed, 15 Apr 2015 17:06:10 GMT

I can see on the web and download. Could you just refresh?

Re: conditional sum based on two input box

Wed, 15 Apr 2015 17:11:51 GMT

Please change the view of the messages in your qlik view in box in the full one in this way you will see the attachment

Re: conditional sum based on two input box

Wed, 15 Apr 2015 17:14:01 GMT

Go to settings -. variable overview - add variable

To check quickly what is the output - goto input box or text object and view the variable values

Text Object : = $(vname)

Re: conditional sum based on two input box

Wed, 15 Apr 2015 18:05:16 GMT

In Qlik is not necessary to define the type of data.

is chosen automatically based on the contents of the field or variable.

Instead in Set Analysis is preferable to call in order to avoid misinterpretation.

Now I am in train for two to three hours.

If you have not solved before , I will look willingly qvw you sent

Re: conditional sum based on two input box

Wed, 15 Apr 2015 19:20:34 GMT

Dear Gennaro,

Unfortunately I have not solved. Please help

Inviato da iPhone

Il giorno 15/apr/2015, alle ore 20:05, Gennaro Voto <qcwebmaster@qlikview.com<mailto:qcwebmaster@qlikview.com>> ha scritto:

Qlik Community <https://community.qlik.com/?et=watches.email.thread>

conditional sum based on two input box

risposta da Gennaro Voto<https://community.qlik.com/people/g.voto76?et=watches.email.thread> su New to QlikView - Visualizza la discussione completa<https://community.qlik.com/message/754537?et=watches.email.thread#754537>

Re: conditional sum based on two input box

Wed, 15 Apr 2015 22:07:31 GMT

Hi Massimo,

the file you attached is not correct.
It contains only a few lines of the script to load, there is no tab or object.

For this reason I simulated your data, roughly based upon the information provided in different post, and I built with simulated data analysis with correct formulas.

Let me know if they work, otherwise provide me a qvw with data and the table or chart where I can enter formulas and try on your data.

Regards
Gennaro

Re: conditional sum based on two input box

Thu, 16 Apr 2015 09:20:31 GMT

Dear Gennaro,

from your I understand you changed the " with the ' symbol. Unfortunately this not worked for my case. I try to attach again the file. please let me know if now is OK.

Thank you

Re: conditional sum based on two input box

Thu, 16 Apr 2015 12:51:59 GMT

Hi Max,

I saw the new attach and is now complete with tabs and data.
I tried to apply the formulas and to recreate variables and objects, but I'm afraid that there is some problem in the load, so I do not understand how Qlik interpret date values that come from two different sources (QVD and xlsx).

You could try to create a new field during load data QW_SALDI.QVD and cast to date and giving it the data type, so you can use the new field in the Set Analysis to check the data content.

If you want I can fix it myself, but you should send a subset of data (reduced QVD and .xlsx), so to do the tests directly on your data via reload script.

Bye
Gennaro

Re: conditional sum based on two input box

Thu, 16 Apr 2015 15:51:39 GMT

Ciao Gennaro,

many thanks for your help. After some investigation I understand that the problems comes from the oracle DB. Bt unable to understand how resolve. The point is n the filed DATE_REF that under oracle comes with the date format DD-MMM-YYYY. If I try to export the DB on an excel file and use that one all works fine.

I also tried in the load script to use the sintax:

date#(DATE_REF,'DD/MM/YYYY') AS m_date

but the result is that m_date is shown with number and the comparison with the variable doesn't work.

So if I send you a couple of date it will work just because the dield date will be "normalized".

Are you able to tell me any additional suggestion to try?

It my be the next step is to change something in the conditional sum formula

SUM({$<[m_date]={'$(=Date(VAR_DATA2))'}>} Balance_CTV2)

Thank you

Re: conditional sum based on two input box

Thu, 16 Apr 2015 16:14:24 GMT

Hi Massimo,

use date#(DATE_REF,'DD/MM/YYYY') AS m_date in script, the road is right, if your dates are represented by numbers is okay.

Eliminates the variables that you have created, replacing them with the new variables and using the calendar object set its value.
Variables will be set automatically with the number corresponding to the date.

The attached file is your file, already modified with the creation of new variables, which are measured with numbers corresponding to the dates.
There are also the correct formulas both for the description of the columns that for the calculation of the sum of BALANCE_CTV2

I hope help you solve the problem permanently

Bye

Gennaro

Re: conditional sum based on two input box

Fri, 17 Apr 2015 08:18:48 GMT

Dear Gennaro,

many thanks for your valuable support. yes we got one step more but not yet solved.

It seems that once done the date#(DATE_REF,'DD/MM/YYYY') AS m_date the only way to see the m_date values is to set it with the sintax date#. For example I've made a text box to see how the value is shown with his sintax.

date#(m_date)& '\n' & 'Data 2: ' & (VarData2)

The sum formule still not working. i tried different ways to have it:

ORIGINAL one

SUM({$<m_date={'$(=Date(VarData2))'}>} Balance_CTV2)

ALSO TRIED WITH:

=SUM({$<date#(m_date)={'$(=(VarData1))'}>} Balance_CTV2)

I think we need to change the sintax of the SUM formula but unsure how.

Thank you

Re: conditional sum based on two input box

Marcellino_Groothof — Fri, 17 Apr 2015 09:07:28 GMT

Hi,

sum(if(DATE_REF = VAR_DATA2, Balance_CTV2) ) check of both data vars are identical

instead

=SUM({$<DATE_REF={"$(=VAR_DATA2)"}>}Balance_CTV2)