Help with correcting my expression.

[#1]

Hello guys,

I'm in a dire state right now been busting my brain to get this simple expression done right. Consider this case :

When I select a Country, my tDSO field displays the data correctly as per image below:

The same also resulted the correct data when a Country and PostingYear are selected as per image below:

As you can see from both images, the data displayed for the particular country up untill 2014 - Apr.

But when I select Country - PostingYear and PostingMonth say Apr, the data in tDSO column dissapeared as per image below:

This is really weird because no matter how I look at it logically the expression is correct. The expression for the tDSO column is as follows;

if(tAROpenBal >= tRevUsed and tARCloseBal > 0
,Days
,if(tARCloseBal < tRevUsed
,(tAROpenBal / tRevUsed * Days)))

Attached also is a file that I'm currently working on for your kind review.

Thank you so much in advance guys. Hope to hear from you all soon

[tresesco]

Not sure if understood your issue. But, it seems fine to me. When you don't select any months, all three month values are displayed, when select a partiluar month the corresponding value is displayed. Take a list box for Days field and see the associated data behaviour.

[#3]

Hello tresesco,

Actually the tDSO field should get the Days value corresponding to its row. Now that you mentioned it, yes it seems that the Days in my expression was evaluated based on the selected Month. Is there anyway for me to alter my expression so that the Days value is taken based on the value of its corresponding row in the pivot table?

Thank you so much for your kind attention.

[lironbaram]

hi

didn't really understood the problem

if you select april 2014 then you only see result to that month

what is the problem with this

this is the basic way qlikview works

what do you looks to achieve in the chart

[#5]

Hello Liron,

What I am trying to achieve is that the tDSO field should get the Days value corresponding to its row. In your opinion is there anyway for me to alter my expression so that the Days value is taken based on the value of its corresponding row in the pivot table?

if(tAROpenBal >= tRevUsed and tARCloseBal > 0
,Days <-- Days should correspond to its row in the table chart
,if(tARCloseBal < tRevUsed
,(tAROpenBal / tRevUsed * Days)))

Thank you so much for your kind attention.

[jagan · Accepted solution]

Hi,

Use Days as expression instead of Dimension.  Please find attached file for solution.

Regards,

jagan.

[#7]

Hi Jagan,

Thank you for you kind attention. The expression works! But can you explain to me I am kinda blurry as to what was that causes the tDSO expression not working when I'm using Days as a dimension when it clearly displays the Days (if as dimension) regardless of what Month/Year I selected.

Again thank you for your kind attention and not to forget tresesco and Liron too

[jagan]

Hi,

When you use Days in Dimension, it filters the Days based on the selection, the same when you use it in expression and exclude the selection by using Set Analysis this works.

Hope this helps you.

Regards,

jagan.