Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
poluvidyasagar
Creator II
Creator II

Text box with IF function?

Hi,

I am trying to achieve the following:

   

Part NumDemand Day1Demand Day2DemandDay3Balance
9006021101010100
906457815202050
904818325204545
908663520455050
906574950302060
901299630203080
902849420205080
901811050505090
907815850202010

My Dimensions are:

PartNum

Balance

Variables:

vDays ( ranges between 1 and 3 because of 3 day demand)

Expressions:

1. Demand =

=if(vDays=1,DemandDay1,

if(vDays=2,DemandDay1+DemandDay2,

if(vDays=3,DemandDay1+DemandDay2+DemandDay3)))

2. Check

if(Balance<Demand,'Alert','OK')

Both these expressions worked OK. This is to test when a given part is going to be shortage. User can select number of days of demand using the input box of variable.

However, I would like to count the number of part shortages in text box based on user defined vdays. I have added the formula , however it does not seem to work. Check attached file.


Can someone help?

Thanks,
Vidya

1 Solution

Accepted Solutions
vishsaggi
Champion III
Champion III

Try this?

='Count of Part Shortages' &Chr(13)&

Count(DISTINCT if(Balance<if(vDays=1,DemandDay1,

         if(vDays=2,DemandDay1+DemandDay2,

             if(vDays=3,DemandDay1+DemandDay2+DemandDay3))), [Part Num]))

View solution in original post

5 Replies
vishsaggi
Champion III
Champion III

Try this?

='Count of Part Shortages' &Chr(13)&

Count(DISTINCT if(Balance<if(vDays=1,DemandDay1,

         if(vDays=2,DemandDay1+DemandDay2,

             if(vDays=3,DemandDay1+DemandDay2+DemandDay3))), [Part Num]))

poluvidyasagar
Creator II
Creator II
Author

Hi Vishwa,

It worked out.


However, I have another issue.

My Balance comes from a different file.

Balance:


Load * Inline [


Part Number, PackID, Qty

9006021, A1, 50

9064578, A2, 25

9048183, A3, 23

9086635, A4, 25

9065749, A5, 30

9012996, A6, 40

9028494, A7, 40

9018110, A8, 45

9078158, A9, 50

9006021, A10, 50

9064578, A11, 25

9048183, A12, 23

9086635, A13, 25

9065749, A14, 30

9012996, A15, 40

9028494, A16, 40

9018110, A17, 45

9078158, A18, 50

];


So i used Sum(qty) instead of Balance in text box. But it gave error saying that nested aggregation is not allowed.


How do i fix this?


Check attached.


Thanks,

Vidya

vishsaggi
Champion III
Champion III

Yes, you cannot use two aggr functions inside each other without aggregating on some dimensions so use something like:

='Count of Part Shortages' &Chr(13)&

Count(DISTINCT Aggr(if(Sum(Qty)<if(vDays=1,DemandDay1,

                        if(vDays=2,DemandDay1+DemandDay2,

                            if(vDays=3,DemandDay1+DemandDay2+DemandDay3))),[Part Number]), [Part Number]))

poluvidyasagar
Creator II
Creator II
Author

Thanks Vishwa

It worked

vishsaggi
Champion III
Champion III

No Problem.